关于 select count 的问题，有耐心的高手进来帮帮忙

[xiessss]

比如现在这样一张表qq
a b c
1 1 1
1 1 2
1 2 1
1 3 3
2 1 1
2 2 2
2 3 1
3 1 2
3 3 3
我想用ado控件统计 a相同的情况下每个不同的b各有多少个,比如统计b=1的语句：
Adodc1.RecordSource = "select a,count(b) as b1 from qq where b='1' group by a”
结果是：
a b1
1 2
2 1
3 1
我现在想在这基础上再实现统计b=2以及b=3的情况， 及最后结果显示为：
a b1 b2 b3
1 2 1 1
2 1 1 1
3 1 0 1
请问要实现这个统计结果的语句怎么写呢？最好能给个完整的过程，越具体越好，菜鸟在此谢过~

[threenewbee]

Adodc1.RecordSource = "select a,count(b) as b1 from qq where b='1' group by a, group by b”

[xiessss]

[Quote=引用 1 楼 caozhy 的回复:]
Adodc1.RecordSource = "select a,count(b) as b1 from qq where b='1' group by a, group by b”
[/Quote]
似乎有语法错误吧？

[threenewbee]

Adodc1.RecordSource = "select a,count(b) as b1 from qq group by a group by b”

[xiessss]

多谢两位帮忙，问题已经解决~

[chinaboyzyq]

//ACCESS

select c.a,c.b1,iif(isnull(c.b2),0,c.b2) as b2,d.b3 from 
(select a.a,a.b1,b.b2 from
((select a,count(b) as b1 from qq where b='1' group by a) a
left join
(select a,count(b) as b2 from qq where b='2' group by a) b on a.a=b.a)) c
left join
(select a,count(b) as b3 from qq where b='3' group by a) d on c.a=d.a

//SQL SERVER

SELECT c.a, c.b1, ISNULL(c.b2, 0) AS b2, d.b3
FROM (SELECT a.a, a.b1, b.b2
        FROM ((SELECT a, COUNT(b) AS b1
                FROM qq
                WHERE b = '1'
                GROUP BY a) a LEFT JOIN
                  (SELECT a, COUNT(b) AS b2
                 FROM qq
                 WHERE b = '2'
                 GROUP BY a) b ON a.a = b.a)) c LEFT OUTER JOIN
          (SELECT a, COUNT(b) AS b3
         FROM qq
         WHERE b = '3'
         GROUP BY a) d ON c.a = d.a