SQL: 巧妙使用CASE WHEN实现查询

案例

案例：创建表并插入公司名，性别，人数，SQL如下。
要求：统计不同公司，不同性别的员工人数。

DROP TABLE IF EXISTS staff_situation; 
CREATE TABLE staff_situation( 
    company VARCHAR(8), 
    gender VARCHAR(8), 
    num INT 
) 
ENGINE = InnoDB 
DEFAULT CHARSET = utf8; 
INSERT INTO 
staff_situation (company,gender,num) 
VALUE ('A','male',163) 
,('A','female',142) 
,('B','male',98) 
,('B','female',116) 
,('C','male',234) 
,('C','female',196);

方法一: 如果使用多表连接：

SELECT m.company ,
                m.num_male ,
                f.num_female 
FROM ( 
    SELECT company 
                ,SUM(num) AS num_male 
    FROM staff_situation 
    WHERE gender = 'male' 
    GROUP BY company 
) AS m 
INNER JOIN ( 
    SELECT company 
    ,SUM(num) AS num_female 
    FROM staff_situation 
    WHERE gender = 'female' 
    GROUP BY company 
) AS f 
ON m.company = f.company;

方法二：使用CASE WHEN

SELECT company 
 ,SUM(CASE WHEN gender='male' THEN num ELSE 0 END) AS num_male 
 ,SUM(CASE WHEN gender='female' THEN num ELSE 0 END) AS num_female 
FROM staff_situation 
GROUP BY company;

对比发现，使用CASE WHEN可以减少代码的书写量，注意不要忘记写END。