PostgreSQL 进阶教程 1-1 CASE表达式

[toc]

第1章 神奇的SQL

1-1 CASE表达式

1-1.1 CASE表达式

简单CASE表达式

CASE sex
    WHEN '1' THEN '男',
    WHEN '2'THEN '女'
    ELSE '其他' 
    END

搜索CASE表达式

CASE WHEN sex ='1' THEN '男'
     WHEN sex ='2' THEN '女'
     ELSE '其他'
     END

两者的执行结果是相同的。简单CASE表达式正如其名，能实现的事比较简单。
简单CASE能做到的事情，搜索CASE表达式也能做到，所以尽量用搜索CASE表达式来写。

注意事项：

1. 统一各分支返回的数据类型（不要出现某分支返回数值，另一分支返回字符串）
2. 不要忘了写 END （绝大部分未写END导致的错误）
3. 养成写 ELSE 子句的习惯（如果不写ELSE，返回值为NULL，如果语法有错，不易查找原因。）

将已有编号方式转换为新的方式并统计

例如有一张表，来统计北京不同地区的人口数量，想要将要以不同郊区的人口数量来统计。

首先按照Code1.01进行数据环境

Code 1.01

CREATE TABLE code1_01
(area VARCHAR(32) NOT NULL,
population INTEGER NOT NULL);

INSERT INTO code1_01 VALUES 
('东城',25000),('西城',35000),('昌平',6000),('大兴',5000),('怀柔',1000),('密云',1400);

用简单CASE表达式：
Code 1.02

SELECT CASE area
    WHEN  '东城' THEN '市区'
    WHEN  '西城' THEN '市区'
    WHEN  '昌平' THEN '近郊'
    WHEN  '大兴' THEN '近郊'
    WHEN  '怀柔' THEN '远郊'
    WHEN  '密云' THEN '远郊'
    ELSE '其他' 
    END AS area,
    SUM(population)
FROM code1_01
GROUP BY  CASE area
    WHEN  '东城' THEN '市区'
    WHEN  '西城' THEN '市区'
    WHEN  '昌平' THEN '近郊'
    WHEN  '大兴' THEN '近郊'
    WHEN  '怀柔' THEN '远郊'
    WHEN  '密云' THEN '远郊'
    ELSE '其他' 
    END
;

用搜索CASE表达式

Code1.03

SELECT CASE 
    WHEN area = '东城'THEN '市区'
    WHEN area = '西城'THEN '市区'
    WHEN area = '昌平'THEN '近郊'
    WHEN area = '大兴'THEN '近郊'
    WHEN area = '怀柔'THEN '远郊'
    WHEN area = '密云'THEN '远郊'
    ELSE '其他' END AS area,
    SUM(population)
FROM code1_01
GROUP BY CASE 
    WHEN area = '东城'THEN '市区'
    WHEN area = '西城'THEN '市区'
    WHEN area = '昌平'THEN '近郊'
    WHEN area = '大兴'THEN '近郊'
    WHEN area = '怀柔'THEN '远郊'
    WHEN area = '密云'THEN '远郊'
ELSE '其他' END;

进一步简化
Code 1.04

SELECT CASE 
    WHEN area IN ('东城','西城')THEN '市区'
    WHEN area IN ('昌平','大兴')THEN '近郊'
    WHEN area IN ('怀柔','密云')THEN '远郊'
    ELSE '其他' END AS area,
    SUM(population)
FROM code1_01
GROUP BY CASE 
    WHEN area IN ('东城','西城')THEN '市区'
    WHEN area IN ('昌平','大兴')THEN '近郊'
    WHEN area IN ('怀柔','密云')THEN '远郊'
ELSE '其他' END;

如果想要得到如下的结果

Code 1.05

SELECT CASE 
    WHEN population <=5000 THEN  '小型城市'
    WHEN population >5000 AND population<=10000  THEN '中型城市'
    WHEN population >10000  THEN '大型城市'
    ELSE '其他' END AS "城市等级",
count(area) AS "数量"
FROM code1_01
GROUP BY CASE 
    WHEN population <=5000 THEN  '小型城市'
    WHEN population >5000 AND population<=10000  THEN '中型城市'
    WHEN population >10000  THEN '大型城市'
    ELSE '其他' END;

这个方法也很好，但是SELECT和GROUP BY需要写两条一模一样的CASE函数，后期修改容易出错。

但是对于某些关系型数据库（PostgreSQL/MySql）来说可以采取以下写法。

Code 1.06

SELECT CASE 
    WHEN population <=5000 THEN  '小型城市'
    WHEN population >5000 AND population<=10000  THEN '中型城市'
    WHEN population >10000  THEN '大型城市'
    ELSE '其他' END AS "城市等级",
count(area) AS "数量"
FROM code1_01
GROUP BY "城市等级";

正常情况下GROUP BY 语句比SELECT先执行，所以GROUP BY引用SELECT的别名是不符合规定的。
而PostgreSQL/MySql数据库中，在执行GROUP BY 语句中，先会在SELECT进行扫描（不计算），所以在这些数据库中执行，不会报错。
但是不符合SQL规定，所以不建议采取这种方式，但是会提高语句的可读性。

用一条SQL语句进行不同条件的统计

例如如下图，左边的表为数据，想要达到右边的效果

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dULQa50R-1673180083510)(image/02笔记SQL进阶教程作者MICK/1673163916106.png)]

Code 1.07 创建数据环境

CREATE TABLE poptbl2 
(area VARCHAR(32) NOT NULL,sex char(2) NOT NULL,population INTEGER NOt NULL);

INSERT INTO poptbl2 VALUES 
('东城','1',55),
('东城','2',65),
('西城','1',85),
('西城','2',95),
('海淀','1',78),
('海淀','2',91),
('丰台','1',86),
('丰台','2',71),
('昌平','1',105),
('昌平','2',115),
('房山','1',95),
('房山','2',87),
('密云','1',93),
('密云','2',88),
('平谷','1',92),
('平谷','2',94);

通常做法是利用WHERE语句，形成两个SELECT,并进行UNION。

Code 1.08 利用WHERE

SELECT area,SUM(population) FROM poptbl2 WHERE sex='1' GROUP BY area
UNION
SELECT area,SUM(population) FROM poptbl2 WHERE sex='2' GROUP BY area;

用这种方法，工作量并没有减少，而且SQL语句也变的很长，而如果用CASE表达式，可以用一条简单的SQL语句搞定。

Code 1.09

SUM(CASE WHEN sex='2' THEN population ELSE 0 END) AS cnt_m,
SUM(CASE WHEN sex='1' THEN population ELSE 0 END) AS cnt_f
FROM poptbl2
GROUP BY area;