[PSQL] CASE表达式

63 阅读 0 评论 42 点赞

我是靠谱客的博主愤怒钢铁侠，最近开发中收集的这篇文章主要介绍[PSQL] CASE表达式，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

《SQL进阶教程》 MICK 1-1 CASE表达式笔记

关于用于创建示例用表的SQL语句和示例代码等，请参考如下网站。
http://www.ituring.com.cn/book/1813（请点击“随书下载”下载中文版相关资料）

---------------------------------------------------------------

说明：如下笔记中的测试基于postgresql14

命令行连接本地PSQL： psql -U <username> -d <dbname> -h 127.0.0.1 -W

CASE表达式概述

两种写法：简单CASE表达式、搜索CASE表达式

使用CASE表达式的注意事项

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

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

在UPDATE语句里进行条件分支

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表达式的注意事项

注意事项1：使用WHEN子句时要注意条件的排他性
在发现为真的WHEN子句时，CASE表达式的真假值判断就会中止，剩余的WHEN子句会被忽略。

注意事项2：统一各分支返回的数据类型

注意事项3：不要忘了写END

注意事项4：养成写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);

PopTbl内容如下

test=> select * from Poptbl;
 pref_name | population
-----------+------------
 德岛      |        100
 香川      |        200
 爱媛      |        150
 高知      |        200
 福冈      |        300
 佐贺      |        100
 长崎      |        200
 东京      |        400
 群马      |         50
(9 rows)

test=>

首先看如下CASE语句输出结果

test=> SELECT CASE pref_name
             WHEN '德岛' THEN '四国'
             WHEN '香川' THEN '四国'
             WHEN '爱媛' THEN '四国'
             WHEN '高知' THEN '四国'
             WHEN '福冈' THEN '九州'
             WHEN '佐贺' THEN '九州'
             WHEN '长崎' THEN '九州'
             ELSE '其他' END AS district
  FROM PopTbl;
 district
----------
 四国
 四国
 四国
 四国
 九州
 九州
 九州
 其他
 其他
(9 rows)

test=>

【例】把县编号转换成地区编号

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

test=>

GROUP BY 后面一大段也可以写作district，如下

test=> 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
----------+-----
 九州     | 600
 四国     | 650
 其他     | 450
(3 rows)

test=>

说明：这里的GROUP BY子句使用的正是SELECT子句里定义的列的别称district。但是严格来说，这种写法是违反标准SQL的规则的。因为GROUP BY子句比SELECT语句先执行，所以在GROUP BY子句中引用在SELECT子句里定义的别称是不被允许的。事实上，在Oracle、DB2、SQL Server等数据库里采用这种写法时就会出错。不过也有支持这种SQL语句的数据库，例如在PostgreSQL和MySQL中，这个查询语句就可以顺利执行。

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

/* 用一条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;

test->  GROUP BY pref_name;
 pref_name | cnt_m | cnt_f
-----------+-------+-------
 长崎      |   125 |   125
 高知      |   100 |   100
 佐贺      |    20 |    80
 东京      |   250 |   150
 香川      |   100 |   100
 爱媛      |   100 |    50
 德岛      |    60 |    40
 福冈      |   100 |   200
(8 rows)

test=>

在UPDATE语句里进行条件分支

假设现在需要根据以下条件对该表的数据进行更新。
1. 对当前工资为 30 万日元以上的员工，降薪 10%。
2. 对当前工资为 25 万日元以上且不满 28 万日元的员工，加薪 20%。
按照这些要求更新完的数据应该如下表所示。

注意：不能依次使用两个UPDATE语句完成，因为第二个UPDATE语句会基于第一个UPDATE语句更新后的结果操作。

Salaries建表语句

/* 员工工资信息表 */
CREATE TABLE Salaries
(name VARCHAR(32) PRIMARY KEY,
 salary INTEGER NOT NULL);

INSERT INTO Salaries VALUES('相田', 300000);
INSERT INTO Salaries VALUES('神崎', 270000);
INSERT INTO Salaries VALUES('木村', 220000);
INSERT INTO Salaries VALUES('齐藤', 290000);

更新语句如下

/* 用CASE表达式写正确的更新操作 */
UPDATE Salaries
   SET salary = CASE WHEN salary >= 300000
                     THEN salary * 0.9
                     WHEN salary >= 250000 AND salary < 280000
                     THEN salary * 1.2
                     ELSE salary END;