sql进阶教程01-case表达式

新手用 WHERE 子句和 HAVING 子句进行条件分支，高手用 SELECT 子句进行条件 分支。

1.1 基础使用

CASE 表达式是不依赖于具体 数据库的技术，所以可以提高 SQL 代码的可移植性。

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sex` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1 男 2女',
  `name` varchar(255) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

INSERT INTO `sql_advance`.`users` (`id`, `sex`, `name`) VALUES (1, 2, 'levon');
INSERT INTO `sql_advance`.`users` (`id`, `sex`, `name`) VALUES (2, 1, 'fly');
INSERT INTO `sql_advance`.`users` (`id`, `sex`, `name`) VALUES (3, 1, 'xuan');
INSERT INTO `sql_advance`.`users` (`id`, `sex`, `name`) VALUES (4, 1, 'yuan');
INSERT INTO `sql_advance`.`users` (`id`, `sex`, `name`) VALUES (5, 2, 'haha');

SELECT name,(CASE WHEN sex = 1 THEN '男' WHEN sex = 2 THEN '女' ELSE '其他' END)sex FROM users
/*
levon	女
fly	男
xuan	男
yuan	男
haha	女
*/

如果 CASE 表达式里没有明确指定 ELSE 子句， 执行结果会被默认地处理成ELSE NULL。所以一定要写ELSE。

1.2 select 不同条件

/* 用一条SQL语句进行不同条件的统计 */
SELECT pref_name,
       /* 男性人口 */
       SUM( CASE WHEN sex = '1' THEN population ELSE 0 END) AS cnt_m,
       /* 女性人口 */
       SUM( CASE WHEN sex = '2' THEN population ELSE 0 END) AS cnt_f
  FROM PopTbl2
 GROUP BY pref_name;
 
 
 /*
东京	250	150
佐贺	20	80
德岛	60	40
爱媛	100	50
福冈	100	200
长崎	125	125
香川	100	100
高知	100	100
 */

这个技巧可贵的地方在于，它能将 SQL 的查询结果转换为二维表的格式。

如果只是简单地用 GROUP BY 进行聚合，那么查询后必须通过程序将结果的格式转换一下，才能使之成为交叉表。

1.3 update 调换值

/* 用CASE表达式调换主键值 */
UPDATE SomeTable
   SET p_key = CASE WHEN p_key = 'a'
                    THEN 'b'
                    WHEN p_key = 'b'
                    THEN 'a'
                    ELSE p_key END
 WHERE p_key IN ('a', 'b');

1.4 强大表达能力

在 CASE 表达式里，我们可以使用 BETWEEN、LIKE 和 <、 > 等 便利的谓词组合，以及能嵌套子查询的 IN 和 EXISTS 谓词。因此，CASE 表达式具有非常强大的表达能力。

作为表达式，CASE 表达式在执行时会被判定为一个固定值，因此它 可以写在聚合函数内部;也正因为它是表达式，所以还可以写在 SELECE 子句、GROUP BY 子句、WHERE 子句、ORDER BY 子句里。简单点说，在能 写列名和常量的地方，通常都可以写 CASE 表达式。

1.5 练习

• 从多列数据里选出最大值该怎么做呢

SELECT `key`, CASE WHEN x > y THEN x ELSE (CASE WHEN y > z THEN y ELSE z END) END greatest FROM Greatests

 /*
 A	3
 B	5
 C	7
 D	8
 */

• 用 ORDER BY 生成“排序”列, 使得结果按照 B-A-D-C 这样的指定顺 序进行排列。

  SELECT `key` FROM Greatests ORDER BY CASE `key`
              WHEN 'B' THEN 1
              WHEN 'A' THEN 2
              WHEN 'D' THEN 3
              WHEN 'C' THEN 4
              ELSE NULL END;

• 转换行列 (1.2)

   SELECT CASE WHEN sex = 1 THEN '男' ELSE '女' END 性别,
         SUM(population) AS total,
         SUM(CASE WHEN pref_name = '德岛' THEN population ELSE 0 END) AS 德岛,
         SUM(CASE WHEN pref_name = '香川' THEN population ELSE 0 END) AS 香川,
         SUM(CASE WHEN pref_name = '爱媛' THEN population ELSE 0 END) AS 爱媛,
         SUM(CASE WHEN pref_name = '高知' THEN population ELSE 0 END) AS 高知,
         SUM(CASE WHEN pref_name IN ('德岛', '香川', '爱媛', '高知') THEN population ELSE 0 END) AS 四国
    FROM PopTbl2
   GROUP BY sex;
   
   /*
  男	855	60	100	100	100	360
  女	845	40	100	50	100	290
   */

1.6 总结

1. 在 GROUP BY 子句里使用 CASE 表达式，可以灵活地选择作为聚合 的单位的编号或等级。这一点在进行非定制化统计时能发挥巨大的威力。 2. 在聚合函数中使用 CASE 表达式，可以轻松地将行结构的数据转换
   成列结构的数据。
2. 相反，聚合函数也可以嵌套进 CASE 表达式里使用。
3. 相比依赖于具体数据库的函数，CASE 表达式有更强大的表达能力
   和更好的可移植性。
4. 正因为 CASE 表达式是一种表达式而不是语句，才有了这诸多优点。