我有两个查询要组合成单个输出,我不能使用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