表 A
ID English Math Chinese Art
1 90 95 81 70
2 100 60 90 83
3 59 80 90 75请统计出英语成绩及格(60分,及以上)人数,数学成绩80以上(包含80)人数,以及语文成绩的平均分数,和美术成绩的总分数.
(注意:使用一条sql语句实现)
ID English Math Chinese Art
1 90 95 81 70
2 100 60 90 83
3 59 80 90 75请统计出英语成绩及格(60分,及以上)人数,数学成绩80以上(包含80)人数,以及语文成绩的平均分数,和美术成绩的总分数.
(注意:使用一条sql语句实现)
sum(case when math>=80 then 1 else 0 end) as 'math',
avg(chinese) as 'chinese',
sum(art) as 'art'
from A
go
create table a(id int,english int,math int,chinese int,art int)
insert into a
select 1,90,95,81,70
union all select 2,100,60,90,83
union all select 3,59,80,90,75select sum(case when english>=60 then 1 else 0 end) as 'english',
sum(case when math>=80 then 1 else 0 end) as 'math',
avg(chinese) as 'chinese',
sum(art) as 'art'
from Aenglish math chinese art
----------- ----------- ----------- -----------
2 2 87 228(所影响的行数为 1 行)