select a,b from (select a,b,count(*) cnt from tab group by a,b order by cnt desc ) where rownum <= 2
select a,b from (select a,b,count(*) cnt from tab group by a,b order by cnt desc ) where rownum = 1
刚才意思理解错了,下面方法应该可以:SELECT ID,num FROM (SELECT ID, num, ROW_number() over(PARTITION BY ID ORDER BY COUNT(*) DESC) rn FROM c GROUP BY ID, num) WHERE rn = 1 或者: SELECT DISTINCT first_value(ID) over(PARTITION BY ID ORDER BY cnt DESC),first_value(num) over(PARTITION BY ID ORDER BY cnt DESC) FROM (SELECT ID, num, COUNT(*) cnt FROM c GROUP BY ID, num)
select a,b from ( select a.a,a.b,rank()over(partition by a.a order by count(1) desc) cnt from table2006119 a,table2006119 b where a.a=b.a and a.rowid=b.rowid and a.b=b.b group by a.a,a.b) where cnt=1 可能有点复杂,不过暂时也只能相除这么个点子,希望大家批评指正,共同学习!!
对应一下:SELECT a,b FROM (SELECT a, b, ROW_number() over(PARTITION BY a ORDER BY COUNT(*) DESC) rn FROM tab GROUP BY a, b) WHERE rn = 1 或者: SELECT DISTINCT first_value(a) over(PARTITION BY a ORDER BY cnt DESC),first_value(b) over(PARTITION BY a ORDER BY cnt DESC) FROM (SELECT a, b, COUNT(*) cnt FROM tab GROUP BY a, b)
select a,b from ( select a,b,rank()over(partition by a order by count(1) desc) cnt from table2006119 group by a,b) where cnt=1
其实我的思路与duanzilin(寻) 这位兄弟差不多!!还是他的思路比较多!!学习呀!!
SQL> SELECT * FROM TEST.T;A B ---------- ---------- 张三 50 张三 50 张三 50 张三 60 李四 90 李四 90 李四 100 王五 80已选择8行。 WITH KK AS (SELECT A,B,COUNT(*) AS CNT FROM TEST.T GROUP BY A,B) SELECT A,B FROM KK M WHERE M.CNT=( SELECT MAX(CNT) FROM KK N WHERE M.A=N.A );A B ---------- ---------- 李四 90 王五 80 张三 50
where rownum <= 2
where rownum = 1
FROM (SELECT ID, num, ROW_number() over(PARTITION BY ID ORDER BY COUNT(*) DESC) rn FROM c GROUP BY ID, num)
WHERE rn = 1 或者:
SELECT DISTINCT first_value(ID) over(PARTITION BY ID ORDER BY cnt DESC),first_value(num) over(PARTITION BY ID ORDER BY cnt DESC)
FROM (SELECT ID, num, COUNT(*) cnt FROM c GROUP BY ID, num)
from (
select a.a,a.b,rank()over(partition by a.a order by count(1) desc) cnt
from table2006119 a,table2006119 b
where a.a=b.a
and a.rowid=b.rowid
and a.b=b.b
group by a.a,a.b)
where cnt=1 可能有点复杂,不过暂时也只能相除这么个点子,希望大家批评指正,共同学习!!
FROM (SELECT a, b, ROW_number() over(PARTITION BY a ORDER BY COUNT(*) DESC) rn FROM tab GROUP BY a, b)
WHERE rn = 1 或者:
SELECT DISTINCT first_value(a) over(PARTITION BY a ORDER BY cnt DESC),first_value(b) over(PARTITION BY a ORDER BY cnt DESC)
FROM (SELECT a, b, COUNT(*) cnt FROM tab GROUP BY a, b)
from (
select a,b,rank()over(partition by a order by count(1) desc) cnt
from table2006119 group by a,b)
where cnt=1
---------- ----------
张三 50
张三 50
张三 50
张三 60
李四 90
李四 90
李四 100
王五 80已选择8行。
WITH KK AS (SELECT A,B,COUNT(*) AS CNT FROM TEST.T GROUP BY A,B)
SELECT A,B
FROM KK M
WHERE M.CNT=(
SELECT MAX(CNT)
FROM KK N
WHERE M.A=N.A
);A B
---------- ----------
李四 90
王五 80
张三 50