还是求sql语句啊，问题，还是没有搞定

[lxpandsq]

如图，有三个表，a1，a2，c1

我希望通过sql语句，可以得到如表D1那样的结果，

后面的字段count是表示出现了几次的计数

[leandzgc]

楼主，你的图有问题啊。地址错了。

[lxpandsq]

[Quote=引用 1 楼 的回复:]
楼主，你的图有问题啊。地址错了。
[/Quote]

这图片传的真纠结，我描述吧。

就是我有一张表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出现的次数。

[luweiyuan01]

select a.name,
(select count(1) from t where name = a.name and t.id <5) count1,
(select count(1) from t where name = a.name and t.id in (5,6)) count2,
(select count(1) from t where name = a.name and t.id>6) count3
from t a
group by a.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 行受影响)


*/