这图片传的真纠结,我描述吧。就是我有一张表A1如下: ID Name01 A02 B03 C04 A 05 B06 B08 A我希望查出来的结果是表A2:name count1 count2 count3A 2 1B 1 2C 1解释下,A2表中的count1,表示在表A1中id小于5的A出现次数为2,B出现次数为1,C出现次数为1.count2表示,id在5-6之间,ABC出现的次数count3表示,id在6以上,ABC出现的次数。
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 行受影响) */
这图片传的真纠结,我描述吧。就是我有一张表A1如下:
ID Name01 A02 B03 C04 A 05 B06 B08 A我希望查出来的结果是表A2:name count1 count2 count3A 2 1B 1 2C 1解释下,A2表中的count1,表示在表A1中id小于5的A出现次数为2,B出现次数为1,C出现次数为1.count2表示,id在5-6之间,ABC出现的次数count3表示,id在6以上,ABC出现的次数。
(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;
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
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 行受影响)
*/