简单语句写不出

[游戏Lan]

有表A
有三字段
ID TITLE CLASS_ID
1 A 2
2 A 2
3 A 1
4 B 2
6 A 3
7 B 2
8 A 3
9 B 1
查询结果
CLASS_ID　　titlenum Anum Bnum
１ 2 1 1
２ 4 2 1
３ 2 1 1
写一SQL语句
实现按CLASS_ID分组　title记录数　A的个数　B的个数

[AgilePicker]

select class_ID,count(title),(select count(title) from 表A where title='A'),
(select count(title) from 表A where title='B') from 表A group by class_ID

[AgilePicker]

对不起，刚才看错了，呵呵，应该这样写

select class_ID,(select count(title) from 表A where class_ID=a.class_ID),(select count(title) from 表A where title='A' and class_ID=a.class_ID),
(select count(title) from 表A where title='B' and class_ID=a.class_ID) from 表A a group by class_ID

[QQMagicer]

select class_ID,
count(title) as titlenum,
(select count(title) from 表A as a where a.class_ID =表A.class_ID and title='A') as Anum,
(select count(title) from 表A as a where a.class_ID =表A.class_ID and title='B') as Bnum
from 表A
group by class_ID

[vivianfdlpw]

select CLASS_ID
,count(1) as 'titlenum'
,sum(case when TITLE='A' then 1 else 0 end) as 'Anum'
,sum(case when TITLE='B' then 1 else 0 end) as 'Bnum'
from A
group by CLASS_ID
order by CLASS_ID

[singlepine]

select
CLASS_ID,
titlenum = sum(TITLE),
Anum = sum(case TITLE when 'A' then 1 else 0 end),
Bnum = sum(case TITLE when 'B' then 1 else 0 end)

from a group by CLASS_ID