id name score subject
1 aaa 80 语文
2 aaa 90 数学
3 bbb 80 语文
4 bbb 50 数学
5 ccc 50 语文
5 ccc 50 数学查询如下结果:
name 不及格科目数 及格科目数
aaa 0 0
bbb 1 1
ccc 2 0
请教高手怎么写
1 aaa 80 语文
2 aaa 90 数学
3 bbb 80 语文
4 bbb 50 数学
5 ccc 50 语文
5 ccc 50 数学查询如下结果:
name 不及格科目数 及格科目数
aaa 0 0
bbb 1 1
ccc 2 0
请教高手怎么写
create table tb(id int,name nvarchar(10),score int,subject nvarchar(10))
insert into tb select 1,'aaa',80,'语文'
insert into tb select 2,'aaa',90,'数学'
insert into tb select 3,'bbb',80,'语文'
insert into tb select 4,'bbb',50,'数学'
insert into tb select 5,'ccc',50,'语文'
insert into tb select 5,'ccc',50,'数学'
go
select name,sum(case when score>=60 then 0 else 1 end)不及格科目数,sum(case when score>=60 then 1 else 0 end)及格科目数
from tb group by name
go
drop table tb
/*
name 不及格科目数 及格科目数
---------- ----------- -----------
aaa 0 2
bbb 1 1
ccc 2 0(3 行受影响)*/
insert into t_1(id,name,score,subject)
select 1,'aaa',80,'语文'
union
select 2,'aaa',90,'数学'
union
select 3,'bbb',80,'语文'
union
select 4,'bbb',50,'数学'
union
select 5,'ccc',50,'语文'
union
select 6,'ccc',50,'数学'select name,sum(case when score < 60 then 1 else 0 end) as 不及格科目数,sum(case when score >= 60 then 1 else 0 end)as 及格科目数
from t_1
group by name
name,sum(case when score>=60 then 0 else 1 end) as 不及格科目数,
sum(case when score>=60 then 1 else 0 end) as 及格科目数
from
tb
group by
name
(
id int ,
name varchar(30),
score int,
subject varchar(30)
)
insert into Score
select 1,'aaa',90,'数学' union
select 2, 'aaa',90, '数学' union
select 3, 'bbb',80, '语文' union
select 4, 'bbb', 50, '数学' union
select 5, 'ccc', 50, '语文' union
select 6, 'ccc', 50, '数学' select name,sum(case when score<60 then 1 else 0 end) as 不及格,sum(case when score>60 then 1 else 0 end) as 及格 from [Score] group by [name]
name 不及格 及格
------------------------------ ----------- -----------
aaa 0 2
bbb 1 1
ccc 2 0(3 行受影响)