[mysql]mysql 不同條件count ，多條件count()及行列加總

參考網址：程式人生 https://www.796t.com/content/1550942407.html

用途：統計各欄次數

mysql total time lse case from pre div clas

create table abc(A int,B int)

Select A,count(B) as total from ABC group by A
Select A,count(B) as total1 from ABC where B > 30 group by A
Select A,count(B) as totlal2 from ABC where B > 20 group by A

.

如何合並這三個查詢？
得到一個查詢結果：
A,total,total1,total2

答：

Select A,
count(B) as total,
sum(case when B > 30 then 1 else 0 end) as total1,
sum(case when B > 20 then 1 else 0 end) as total2 
from ABC group by A

查詢該項目(i1)在該班級該性別的參賽人數
select sum(case when i1='v' then 1 else 0 end) as c1,
       sum(case when i2='v' then 1 else 0 end) as c2,
       sum(case when i3='v' then 1 else 0 end) as c3,
       sum(case when i4='v' then 1 else 0 end) as c4,
       sum(case when i5='v' then 1 else 0 end) as c5

from player where gov_code='701' and player_sex=2

行列加總

行加總：計算該行有多少v
select sum(case when i1='v' then 1 else 0 end) as c1,
       sum(case when i2='v' then 1 else 0 end) as c2,
       sum(case when i3='v' then 1 else 0 end) as c3
from player where gov_code='701' 


列加總：計算該列有多少v
select (s1+s2+s3+s4+s5) as ss from
(select 
    (case when i1='v' then 1 else 0 end) as s1,
    (case when i2='v' then 1 else 0 end) as s2,
    (case when i3='v' then 1 else 0 end) as s3
from player where player_id=1) as abc