我有两个查询要组合成单个输出,我不能使用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
最佳答案
这里最简单的解决方案就是编写一个查询来连接您已经拥有的两个结果集.您可以将查询结果存储在tmp表中并加入临时表,如下所示：
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;