MySQL--case表达式

本文根据《SQL进阶教程》（[日]MICK 吴炎昌/译）所写笔记。

case 表达式概述

我们用case来表达条件分支。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 

由于简单case表达式能写的条件，搜索表达式也能写，且简单case表达式能实现的事情有限，所以一般用搜索case表达式。
需要注意的是，在发现为真的when子句时，case表达式的真假值判断就会中止，剩余的when子句会被忽略。为了引起不必要的混乱，使用when子句时要注意条件的排他性。此外case表达式还要注意以下几点：

• 统一各分支返回的数据类型，各分支返回的数据类型必须一致
• 不要忘了写end
• 养成写else的习惯。else子句是可选的，不写也不会出错，不写else子句时，case表达式的执行结果时null。

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

先创建一张PopTbl表：

CREATE TABLE PopTbl
(pref_name VARCHAR(32) PRIMARY KEY,
 population INTEGER NOT NULL);

INSERT INTO PopTbl VALUES('德岛', 100);
INSERT INTO PopTbl VALUES('香川', 200);
INSERT INTO PopTbl VALUES('爱媛', 150);
INSERT INTO PopTbl VALUES('高知', 200);
INSERT INTO PopTbl VALUES('福冈', 300);
INSERT INTO PopTbl VALUES('佐贺', 100);
INSERT INTO PopTbl VALUES('长崎', 200);
INSERT INTO PopTbl VALUES('东京', 400);
INSERT INTO PopTbl VALUES('群马', 50);

select * from poptbl;
+-----------+------------+
| pref_name | population |
+-----------+------------+
| 东京      |        400 |
| 佐贺      |        100 |
| 德岛      |        100 |
| 爱媛      |        150 |
| 福冈      |        300 |
| 群马      |         50 |
| 长崎      |        200 |
| 香川      |        200 |
| 高知      |        200 |
+-----------+------------+

pref_name是县名。表中，德岛、香川、爱媛、高知属于四国，福冈、佐贺、长崎属于九州，东京、群马属于其他。我们的任务就是将上面三个地区的人口汇总。

SELECT CASE pref_name
             WHEN '德岛' THEN '四国'
             WHEN '香川' THEN '四国'
             WHEN '爱媛' THEN '四国'
             WHEN '高知' THEN '四国'
             WHEN '福冈' THEN '九州'
             WHEN '佐贺' THEN '九州'
             WHEN '长崎' THEN '九州'
             ELSE '其他' END AS district,
       SUM(population)
  FROM PopTbl
 GROUP BY CASE pref_name
             WHEN '德岛' THEN '四国'
             WHEN '香川' THEN '四国'
             WHEN '爱媛' THEN '四国'
             WHEN '高知' THEN '四国'
             WHEN '福冈' THEN '九州'
             WHEN '佐贺' THEN '九州'
             WHEN '长崎' THEN '九州'
             ELSE '其他' END;
+----------+-----------------+
| district | sum(population) |
+----------+-----------------+
| 九州     |             600 |
| 其他     |             450 |
| 四国     |             650 |
+----------+-----------------+

这里的关键在于将select子句里的case表达式复制到group by子句里。
同理，也可以将数值按照适当的级别进行分类统计。例如要按人口数量等级（pop_class)查询：

SELECT CASE WHEN population < 100 THEN '01'
            WHEN population >= 100 AND population < 200 THEN '02'
            WHEN population >= 200 AND population < 300 THEN '03'
            WHEN population >= 300 THEN '04'
            ELSE NULL END AS pop_class,
       COUNT(*) AS cnt
  FROM PopTbl
 GROUP BY CASE WHEN population < 100 THEN '01'
               WHEN population >= 100 AND population < 200 THEN '02'
               WHEN population >= 200 AND population < 300 THEN '03'
               WHEN population >= 300 THEN '04'
               ELSE NULL END;
+-----------+-----+
| pop_class | cnt |
+-----------+-----+
| 01        |   1 |
| 02        |   3 |
| 03        |   3 |
| 04        |   2 |
+-----------+-----+

这个技巧很好用，不过必须在select子句和group by子句这两处写一样的case表达式，太麻烦了!所以可以有如下写法：

mysql> select case pref_name
    ->          when '德岛' then '四国'
    ->          when '香川' then '四国'
    ->          when '爱媛' then '四国'
    ->          when '高知' then '四国'
    ->          when '福冈' then '九州'
    ->          when '佐贺' then '九州'
    ->          when '长崎' then '九州'
    ->      else '其他' end as district,
    ->      sum(population)
    -> from poptbl
    -> group by district;
+----------+-----------------+
| district | sum(population) |
+----------+-----------------+
| 九州     |             600 |
| 其他     |             450 |
| 四国     |             650 |
+----------+-----------------+

这种写法很简便，但是严格来说，这种写法违反标准SQL规则。因为group by子句比select先执行，所以在group by子句中引用select中的别名是不被允许的。这种写法在oracle和sql server、db2中会出错。在postgresql和mysql中没问题。

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

先创建poptbl2表

CREATE TABLE PopTbl2
(pref_name VARCHAR(32),
 sex CHAR(1) NOT NULL,
 population INTEGER NOT NULL,
    PRIMARY KEY(pref_name, sex));

INSERT INTO PopTbl2 VALUES('德岛', '1',	60 );
INSERT INTO PopTbl2 VALUES('德岛', '2',	40 );
INSERT INTO PopTbl2 VALUES('香川', '1',	100);
INSERT INTO PopTbl2 VALUES(