一张表不同字段的统计问题，请求大神

[weixin_36805304]

表如下：


输出为

图片括号内容只是说明，不显示


求大神出现

[alex259]

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，学习一下

[zcs_zzh]

表名为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;

[TheLittlePython]

select A,B,count(1) from table group by A,B

[TheLittlePython]

刚没理解全，改了下 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;

[acen_chen]

看楼主要求了，如果有并列最多是都显示还是只显示一个，如果都显示2楼的就没问题

[qq_30421439]

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;

[weixin_36805304]

引用 6 楼 TheLittlePython 的回复:

刚没理解全，改了下 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;

大神，我不是很理解你的算法，怎么体现图二的B列他C列呢？

[TheLittlePython]

引用 7 楼 weixin_36805304 的回复:

[quote=引用 6 楼 TheLittlePython 的回复:] 刚没理解全，改了下 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;

大神，我不是很理解你的算法，怎么体现图二的B列他C列呢？[/quote]

引用 7 楼 weixin_36805304 的回复:

[quote=引用 6 楼 TheLittlePython 的回复:] 刚没理解全，改了下 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;

大神，我不是很理解你的算法，怎么体现图二的B列他C列呢？[/quote] 里面的查询是对A&B列进行计数，求出唯一的AB值及出现的次数，外层查询就是去得到，A列中的值对应B列中出现次数最多的值，dense_rank允许相同排名存在； 我写的d就是图二的C列

[kakabulusi]

只有通过嵌套查询， 内层嵌套统计 A B 列字母出现的次数，外层以A B 列分组，求次数列的MAX值。