|
1.score表结构如下：
| id |
username |
china |
math |
english |
| 1 |
zhangsan |
45 |
77 |
98 |
| 2 |
lisi |
46 |
54 |
67 |
| 3 |
wangwu |
74 |
63 |
23 |
2.查询各科不及格人数，要求查询结果如下：2.score表结构如下：
3.sql如下几种方式参考：欢迎吐槽，如您有更好更简洁的写法请提供
1. select
sum(china) 'china',
sum(math) 'math',
sum(english) 'english'
from
(
select
count((case when china<60 then 'china' end)) 'china',
count((case when math<60 then 'math' end)) 'math',
count((case when english<60 then 'english'end)) 'english'
from score
group by case
when china<60 then 'china' when math<60 then 'math' when english<60 then 'english' end)
t;
2.select
(select count(*) from score where china<60) 'china',
(select count(*) from score where math<60) 'math',
(select count(*) from score where english<60) 'english'
from score where china<60 or math<60 or english<60 limit 1;
3.select
sum((case china when 'china' then num else 0 end)) 'china',
sum((case china when 'math' then num else 0 end)) 'math',
sum((case china when 'english' then num else 0 end)) 'english'
from
(
select 'china',count(*) 'num' from score where china<60
union all
select 'math',count(*) 'num' from score where math<60
union all
select 'english',count(*) 'num' from score where english<60
) t;
| 
转播
