Question

sql语句分组查询，按条件分组的问题

我需要将表tb中的数据select出来，得到下面第二个表的数据，如何写select语句？
表tb
id a flag class
----------+---------+--------+---------
1 2 1 A
2 2 1 A
3 4 1 A
4 5 2 A
5 3 2 A
6 4 1 A
7 2 1 A
8 3 2 A
9 4 2 A
10 5 3 A
11 5 1 B
12 2 1 B
13 3 1 B
14 4 1 B
15 2 3 B
16 7 3 B
17 3 2 B
18 4 1 B
19 5 1 B
20 2 2 B
21 1 1 B
22 1 1 C
23 2 3 C
24 6 3 C
25 3 2 C
...

我需要选取出如下的表，按class列进行分组，a1,a2,a3字段分别为flag=1、2、3时tb表中a字段的求和
选取后

a1 a2 a3 class
-----------+------------+-----------------+--------------
sum(a) sum(a) sum(a) A
sum(a) sum(a) sum(a) B
sum(a) sum(a) sum(a) C
sum(a) sum(a) sum(a) D
sum(a) sum(a) sum(a) E
sum(a) sum(a) sum(a) F
sum(a) sum(a) sum(a) G
...

select语句怎么写？
已解决:

select sum(a1) as a1,sum(a2) as a2,sum(a3) as a3,class
from
(select sum(a) as a1,0 as a2,0 as a3,class
from tb
group by class
having tb.flag = '1'
union
select 0 as a1,sum(a2) as a2,0 as a3,class
from tb
group by class
having tb.flag = '2'
union
select 0 as a1,0 as a2,sum(a3) as a3,class
from tb
group by class
having tb.flag = '3'
)DERIVEDTBL
group by class

Answer 1

select distinct class,(select count(a) from tb where flag=1 and tb.class = t.class),(select count(a) from tb where flag=2 and tb.class = t.class),(select count(a) from tb where flag=3 and tb.class = t.class) from tb t group by class,flag
嘿嘿，看看结果吧