SQL之case when then用法

case具有两种格式。简单case函数和case搜索函数。

#--简单case函数
case sex
  when '1' then '男'
  when '2' then '女’
  else '其他' end

#--case搜索函数
case when sex = '1' then '男'
     when sex = '2' then '女'
     else '其他' end

场景1：现老师要统计班中，有多少男同学，多少女同学，并统计男同学中有几人及格，女同学中有几人及格，要求用一个SQL输出结果。

SELECT 
    SUM (CASE WHEN STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_COUNT,
    SUM (CASE WHEN STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_COUNT,
    SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_PASS,
    SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_PASS
FROM 
    THTF_STUDENTS

场景2：经典行转列，并配合聚合函数做统计
现要求统计各个城市，总共使用了多少水耗、电耗、热耗，使用一条SQL语句输出结果

输出结果如下:

SELECT 
    E_CODE,
    SUM(CASE WHEN E_TYPE = 0 THEN E_VALUE ELSE 0 END) AS WATER_ENERGY,--水耗
    SUM(CASE WHEN E_TYPE = 1 THEN E_VALUE ELSE 0 END) AS ELE_ENERGY,--电耗
    SUM(CASE WHEN E_TYPE = 2 THEN E_VALUE ELSE 0 END) AS HEAT_ENERGY--热耗
FROM 
    THTF_ENERGY_TEST
GROUP BY
    E_CODE

场景3：根据上图 生成下图 , 该如何写sql语句?

输出结果如下:

select Tdate '日期',
sum(case when Tresulte ='胜' then 1 else 0 end)'胜',
sum(case when Tresulte ='负' then 1 else 0 end)'负' 
from tmp group by Tdate ;