stuid subid score
1 1 65
1 2 58
2 1 58
2 3 98
3 1 85
3 2 56
3 3 90
执行 select subid,count(score) from info where score>60 group by subid
1 2
3 2
因为subid 2 条件不成立所有没有分组统计
怎样才能得到下列效果
1 2
3 2
2 null
或者
1 2
3 2
2 0
1 1 65
1 2 58
2 1 58
2 3 98
3 1 85
3 2 56
3 3 90
执行 select subid,count(score) from info where score>60 group by subid
1 2
3 2
因为subid 2 条件不成立所有没有分组统计
怎样才能得到下列效果
1 2
3 2
2 null
或者
1 2
3 2
2 0
-->> Author: 让你望见影子的墙(HEROWANG)生成测试数据 Date:2009-04-15 07:22:02
IF OBJECT_ID('tb1') IS NOT NULL
DROP TABLE tb1
Go
CREATE TABLE tb1(stuid INT,subid INT,score INT)
Go
INSERT INTO tb1
SELECT 1,1,65 UNION ALL
SELECT 1,2,58 UNION ALL
SELECT 2,1,58 UNION ALL
SELECT 2,3,98 UNION ALL
SELECT 3,1,85 UNION ALL
SELECT 3,2,56 UNION ALL
SELECT 3,3,90
GOSELECT * FROM TB1select subid,count(score) from tb1 where score>60 group by all subid subid (无列名)
1 2
2 0
3 2
if object_id('tb') is not null
drop table tb
go
create table tb([stuid] int,[subid] int,[score] int)
insert tb select 1,1,65
insert tb select 1,2,58
insert tb select 2,1,58
insert tb select 2,3,98
insert tb select 3,1,85
insert tb select 3,2,56
insert tb select 3,3,90
go
select subid,cnt=sum(case when score>60 then 1 else 0 end) from tb1 group by subid
/*
subid cnt
----------- -----------
1 2
2 0
3 2(3 行受影响)
*/