sql统计各科不及格的人数，要求查询结果是一行

怼烎@ 2022-08-20 12:05 189阅读 0赞

1.score表结构如下：

<table style="width:485px; height:152px"> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>username</td> 
   <td>china</td> 
   <td>math</td> 
   <td>english</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>zhangsan</td> 
   <td>45</td> 
   <td>77</td> 
   <td>98</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>lisi</td> 
   <td>46</td> 
   <td>54</td> 
   <td>67</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>wangwu</td> 
   <td>74</td> 
   <td>63</td> 
   <td>23</td> 
  </tr> 
 </tbody> 
</table>

2.查询各科不及格人数，要求查询结果如下：2.score表结构如下：

<table style="width:457px; height:60px"> 
 <tbody> 
  <tr> 
   <td>china</td> 
   <td>math</td> 
   <td>english</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>2</td> 
   <td>1</td> 
  </tr> 
 </tbody> 
</table>

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)

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;