如下表 :
首先按照aa分组,然后按照bb分组,并统计bb所有值的个数,个数从大到小 ,比如 1001中 55有2个,66和77 都1个,得到如下的结果:
aa bb count(bb)
1001 55 2
1001 66 1
1001 77 1
1002 55 3
1003 66 4
1004 66 1
首先按照aa分组,然后按照bb分组,并统计bb所有值的个数,个数从大到小 ,比如 1001中 55有2个,66和77 都1个,得到如下的结果:
aa bb count(bb)
1001 55 2
1001 66 1
1001 77 1
1002 55 3
1003 66 4
1004 66 1
SELECT aa,bb,COUNT(*) AS aabb_cnt FROM tab
GROUP BY aa,bb
;WITH tb_cnt as
(
SELECT aa,bb,COUNT(*) AS aabb_cnt FROM tab
GROUP BY aa,bb
)SELECT a.* FROM tb_cnt a JOIN
(
SELECT aa,MAX(aabb_cnt) AS max_aabb_cnt
FROM tb_cnt
group by aa
)b ON a.aabb_cnt=b.max_aabb_cnt AND a.aa=b.aa