一个表比如有3个字段
a b c
Tom 1 A
Tom 2 B
Tom 1 B
Tom 1 B
Tom 2 B
Tom 1 A
Tom 1 A
Tom 1 B
Tom 1 B
Jack 1 B
最后得到如下的数据
a b字段1的个数 b字段2的个数 c字段A的个数 c字段B的个数
Tom 7 2 3 6
Jack 1 0 0 1一句sql语句能搞定吗?或者多句也行,最好效率高点儿,谢谢~
a b c
Tom 1 A
Tom 2 B
Tom 1 B
Tom 1 B
Tom 2 B
Tom 1 A
Tom 1 A
Tom 1 B
Tom 1 B
Jack 1 B
最后得到如下的数据
a b字段1的个数 b字段2的个数 c字段A的个数 c字段B的个数
Tom 7 2 3 6
Jack 1 0 0 1一句sql语句能搞定吗?或者多句也行,最好效率高点儿,谢谢~
sum(decode(b,1,1,0)) as b字段1的个数,
sum(decode(b,2,1,0)) as b字段2的个数,
sum(decode(c,'A',1,0)) as c字段A的个数,
sum(decode(c,'B',1,0)) as c字段B的个数
from tab
group by a
select a,
sum(case when b=1 then 1 clse 0 end) b字段1的个数,
sum(case when b=2 then 1 clse 0 end) b字段2的个数,
sum(case when c='A' then 1 clse 0 end) c字段A的个数,
sum(case when c='B' then 1 clse 0 end) c字段B的个数
from tb
group by a;
SELECT A,SUM(DECODE(b,1,1,0)),
SUM(DECODE(b,2,1,0)) ,
SUM(DECODE(c,'A',1,0)),
SUM(DECODE(c,'B',1,0)),
FROM TB GROUP BY A
2 union all
3 select 'tom' a,2 b,'b' c from dual
4 union all
5 select 'tom' a,1 b,'b' c from dual
6 union all
7 select 'tom' a,1 b,'b' c from dual
8 union all
9 select 'tom' a,2 b,'b' c from dual
10 union all
11 select 'tom' a,1 b,'a' c from dual
12 union all
13 select 'tom' a,1 b,'a' c from dual
14 union all
15 select 'tom' a,1 b,'b' c from dual
16 union all
17 select 'tom' a,1 b,'b' c from dual
18 union all
19 select 'jack' a,1 b,'b' c from dual
20 )
21 select a,
22 sum(decode(b, 1, 1, 0)) b1,
23 sum(decode(b, 2, 1, 0)) b2,
24 sum(decode(c, 'a', 1, 0)) c1,
25 sum(decode(c, 'b', 1, 0)) c2
26 from a
27 group by a
28 /
A B1 B2 C1 C2
---------------- ---------- ---------- ---------- ----------
tom 7 2 3 6
jack 1 0 0 1
SQL>