MySql数据库中case when then else end用法

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

表结构如下：其中STU_SEX字段，0表示男生，1表示女生。

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语句输出结果

有能耗表如下：其中，E_TYPE表示能耗类型，0表示水耗，1表示电耗，2表示热耗

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

CASE WHEN中使用子查询

根据城市用电量多少，计算用电成本。假设电能耗单价分为三档，根据不同的能耗值，使用相应价格计算成本。
价格表如下：

当能耗值小于10时，使用P_LEVEL=0时的P_PRICE的值，能耗值大于10小于30使用P_LEVEL=1时的P_PRICE的值…

case WHEN energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 0)
    WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 1)
    WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 2) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 2)