oracle 统计查询 统计每列某值的个数

Question

oraclesql数据

Answer 1

select
  'A',
  sum(decode(a.d1,'A',1,0)) as d1,
  sum(decode(a.d2,'A',1,0)) as d2
  
from tab aunionselect
  'B',
  sum(decode(a.d1,'B',1,0)) as d1,
  sum(decode(a.d2,'B',1,0)) as d2
  
from tab a-- 继续union就行了

Answer 2

下面两种方法都可以，第一种好理解，第二种短select 'A' val,sum(decode(D1,'A',1,0)) D1,sum(decode(D2,'A',1,0)) D2,sum(decode(D3,'A',1,0)) D3,sum(decode(D4,'A',1,0)) D4,
sum(decode(D5,'A',1,0)) D5,sum(decode(D6,'A',1,0)) D6 from 表名
union all 
select 'B',sum(decode(D1,'B',1,0)),sum(decode(D2,'B',1,0)),sum(decode(D3,'B',1,0)),sum(decode(D4,'B',1,0)),
sum(decode(D5,'B',1,0)),sum(decode(D6,'B',1,0)) from 表名
union all 
select 'C',sum(decode(D1,'C',1,0)),sum(decode(D2,'C',1,0)),sum(decode(D3,'C',1,0)),sum(decode(D4,'C',1,0)),
sum(decode(D5,'C',1,0)),sum(decode(D6,'C',1,0)) from 表名
union all 
select 'D',sum(decode(D1,'D',1,0)),sum(decode(D2,'D',1,0)),sum(decode(D3,'D',1,0)),sum(decode(D4,'D',1,0)),
sum(decode(D5,'D',1,0)),sum(decode(D6,'D',1,0)) from 表名;select a.val,sum(decode(b.d1,a.val,1,0)) d1,sum(decode(b.d2,a.val,1,0)) d2,sum(decode(b.d3,a.val,1,0)) d3,
sum(decode(b.d4,a.val,1,0)) d4,sum(decode(b.d5,a.val,1,0)) d5,sum(decode(b.d6,a.val,1,0)) d6
from (select 'A' val from dual union select 'B' from dual union select 'C' from dual union select 'D' from dual ) a,
表名 b where a.val=b.d1 or a.val=b.d2 or a.val=b.d3 or a.val=b.d4 or a.val=b.d5 or a.val=b.d6
group by a.val