mysql – 将两个具有不同列数的查询组合在一起

我有两个查询要组合成单个输出,我不能使用UNIOn,因为它们具有不同的列数.

该表是ref,字段id为refid cellid cat,其中包含整数

查询1：查找每个唯一cellid的总行数

SELECT cellid,COUNT(*) totalcount,cat FROM rel GROUP BY cellid

查询2：为每个唯一的cellid查找cat的Mode(最常见值)

SELECT cellid,cat
FROM rel t
GROUP BY cellid,cat
HAVING cat = (
SELECT cat
FROM rel
WHERE cellid = t.cellid
GROUP BY cat
ORDER BY COUNT(*) DESC,cat
LIMIT 1
)

举一个我想要做的例子,我想查询我的表

id | refid | cellid | cat
1  | 1     |  1     | 1
2  | 2     |  2     | 2
3  | 3     |  3     | 4
4  | 1     |  1     | 2
5  | 2     |  1     | 2
6  | 3     |  1     | 3
7  | 1     |  2     | 2
8  | 1     |  1     | 2

并返回

cellid | no_of_rows | Mode_of_cat
 1     |  5         |  2
 2     |  2         |  2
 3     |  1         |  4

SELECT tmp1.cellid,tmp1.rows,tmp2.mode_cat FROM
(
    SELECT cellid,COUNT(*) AS rows
    FROM rel
    GROUP BY cellid
)tmp1
LEFT JOIN
(
    SELECT cellid,cat AS mode_cat
    FROM rel t
    GROUP BY cellid,cat
    HAVING cat = (
        SELECT cat
        FROM rel
        WHERE cellid = t.cellid
        GROUP BY cat
        ORDER BY COUNT(*) DESC,cat
        LIMIT 1
    )
)tmp2
ON tmp1.cellid = tmp2.cellid;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!