求一个sql语句，在线等。

[lxpandsq]

就是我有一张表A1如下：
ID      Name

01      A

02      B

03      C

04      A 

05      B

06      B

08      A

我希望查出来的结果是表A2：

name      count1        count2       count3

A          2                            1

B          1              2

C          1

解释下，A2表中的count1，表示在表A1中id小于5的A出现次数为2，B出现次数为1，C出现次数为1.

count2表示，id在5-6之间，ABC出现的次数

count3表示，id在6以上，ABC出现的次数。

[筱筱澄]

select Name,
sum(case when id<5 then 1 else 0 end) as count1,
sum(case when id>=5 and id<6 then 1 else 0 end) as count2,
sum(case when id>6 then 1 else 0 end) as count3
from tb
group by name

[人生无悔]

select name
	count(case when ID<5 then 1 else null end) count1,
	count(case when ID between 5 and 6 then 1 else null end) count2,
	count(case when ID>6 then 1 else null end) count3
from A1
group by name;

[开着拖拉机泡妞]

select name
    count(case when ID<5 then 1 else null end) count1,
    count(case when ID between 5 and 6 then 1 else null end) count2,
    count(case when ID>6 then 1 else null end) count3
from A1
group by name;

[筱筱澄]

select Name,
sum(case when id<5 then 1 else 0 end) as count1,
sum(case when id>=5 and id<=6 then 1 else 0 end) as count2,
sum(case when id>6 then 1 else 0 end) as count3
from tb
group by name

[筱筱澄]

--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([ID] VARCHAR(2),[Name] VARCHAR(1))
INSERT [tb]
SELECT '01','A' UNION ALL
SELECT '02','B' UNION ALL
SELECT '03','C' UNION ALL
SELECT '04','A' UNION ALL
SELECT '05','B' UNION ALL
SELECT '06','B' UNION ALL
SELECT '08','A'
--------------开始查询--------------------------

select name,
sum(case when id<5 then 1 else 0 end) as count1,
sum(case when id>=5 and id<=6 then 1 else 0 end) as count2,
sum(case when id>6 then 1 else 0 end) as count3
from tb
group by name
----------------结果----------------------------
/* 
name count1      count2      count3
---- ----------- ----------- -----------
A    2           0           1
B    1           2           0
C    1           0           0

(3 行受影响)


*/

[wcb7038]

select name,
sum(case when id < 5 then 1 else 0 end)count1,
sum(case when id >= 5 and id <= 6 then 1 else 0 end)count2,
sum(case when id > 6 then 1 else 0 end)count3
from A1 group by name

[lxpandsq]

[Quote=引用 1 楼 的回复:]
SQL code

select Name,
sum(case when id<5 then 1 else 0 end) as count1,
sum(case when id>=5 and id<6 then 1 else 0 end) as count2,
sum(case when id>6 then 1 else 0 end) as count3
from tb
group ……
[/Quote]

终于看到希望了，不过，显示case附件语法错误，我在看下，结贴

[以学习为目的]

select name
    count(case when ID<5 then 1 else null end) count1,
    count(case when ID between 5 and 6 then 1 else null end) count2,
    count(case when ID>6 then 1 else null end) count3
from A1
group by name;

[中国风]

declare @Tb TABLE ([ID] VARCHAR(2),[Name] VARCHAR(1))
INSERT @Tb
SELECT '01','A' UNION ALL
SELECT '02','B' UNION ALL
SELECT '03','C' UNION ALL
SELECT '04','A' UNION ALL
SELECT '05','B' UNION ALL
SELECT '06','B' UNION ALL
SELECT '08','A'


select 
[Name] ,
isnull(rtrim(sum(case when [ID]<'05' then 1 end)),'') as count1,
isnull(rtrim(sum(case when [ID] in ( '05' , '06') then 1 end)),'') as count2,
isnull(rtrim(sum(case when [ID]>'05' then 1 end)),'') as count3 
from @Tb 
group by [Name]
/*
Name	count1	count2	count3
A	2		1
B	1	2	1
C	1		
*/

[lxpandsq]

[Quote=引用 1 楼 的回复:]
SQL code

select Name,
sum(case when id<5 then 1 else 0 end) as count1,
sum(case when id>=5 and id<6 then 1 else 0 end) as count2,
sum(case when id>6 then 1 else 0 end) as count3
from tb
group ……
[/Quote]


同一个问题，我在两个版面提了问题，效果天差地别啊，这里，真是，高手如云啊，
另一个帖子，没有正确答案，麻烦回复一下，我也好两个帖子都结了，地址是

http://topic.csdn.net/u/20120808/15/38e27e4f-b824-47a7-ba11-a30014864a11.html