表如下：
輸出為
圖片括號內容只是說明，不顯示
求大神出現
uj5u.com熱心網友回復：
with a as (
select '1' fxh,'c' fa, 'e' fb from dual
union
select '2' fxh,'c' fa, 'e' fb from dual
union
select '3' fxh,'c' fa, 'e' fb from dual
union
select '4' fxh,'c' fa, 'f' fb from dual
union
select '5' fxh,'c' fa, 'f' fb from dual
union
select '6' fxh,'d' fa, 'g' fb from dual
union
select '7' fxh,'d' fa, 'g' fb from dual
union
select '8' fxh,'d' fa, 'g' fb from dual
union
select '9' fxh,'d' fa, 'h' fb from dual
)
SELECT b.fa as "第一列",b.fb as "出現最多的",ct as "出現的次數" FROM (
SELECT max(ct)over(partition by fa ) maxct ,a.* FROM (
SELECT count(fb)over(partition by fa ,fb ) ct,fa,fb,row_number()over(partition by fa ,fb order by fa )rn ,fxh
FROM a
) a where a.rn=1
) b where maxct = ct
最近腦子有點抽，寫的可能有些復雜，但是可以完成你的需求，也希望有大腿寫出簡單的sql，學習一下
uj5u.com熱心網友回復：
看樓主要求了，如果有并列最多是都顯示還是只顯示一個，如果都顯示2樓的就沒問題uj5u.com熱心網友回復：
with test as
(select 'c' a,'e' b from dual
union all select 'c' a,'e' b from dual
union all select 'c' a,'e' b from dual
union all select 'c' a,'f' b from dual
union all select 'c' a,'f' b from dual
union all select 'd' a,'g' b from dual
union all select 'd' a,'g' b from dual
union all select 'd' a,'g' b from dual
union all select 'd' a,'h' b from dual)
select a,b,c
from
(select a,b,c,rank() over(partition by a order by c desc ) rn
from
(select a,b,count(b) c from test group by a,b))
where rn=1;
uj5u.com熱心網友回復：
表名為X，SQL如下：SELECT a, b, cnt
FROM (SELECT a, b, cnt, row_number() over(PARTITION BY a ORDER BY cnt DESC) rn
FROM (SELECT a, b, COUNT(b) over(PARTITION BY a, b) cnt FROM x))
WHERE rn = 1;
uj5u.com熱心網友回復：
select A,B,count(1) from tablegroup by A,B
uj5u.com熱心網友回復：
剛沒理解全，改了下
select A, B, d
from (select A,
B,
d,
dense_rank() over(partition by A order by d desc) rnn
from (select A, B, count(1) d from table t group by A, B))
where rnn = 1;
uj5u.com熱心網友回復：
大神，我不是很理解你的演算法，怎么體現圖二的B列他C列呢？uj5u.com熱心網友回復：
里面的查詢是對A&B列進行計數，求出唯一的AB值及出現的次數，外層查詢就是去得到，A列中的值對應B列中出現次數最多的值，dense_rank允許相同排名存在；
我寫的d就是圖二的C列
uj5u.com熱心網友回復：
只有通過嵌套查詢，內層嵌套統計 A B 列字母出現的次數，外層以A B 列分組，求次數列的MAX值。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/66565.html
標籤：基礎和管理
上一篇：oracle卸載問題