SQL基础教程笔记：第六章　函数、谓词、 CASE表达式

目录

各种各样的函数

学习重点

  • 根据用途，函数可以大致分为算术函数、字符串函数、日期函数、转换函数和聚合函数。
  • 函数的种类很多，无需全都记住，只需要记住具有代表性的函数就可以了，其他的可以在使用时再进行查询。

函数的种类

函数大致可以分为以下几种。

  • 算术函数（用来进行数值计算的函数）
  • 字符串函数（用来进行字符串操作的函数）
  • 日期函数（用来进行日期操作的函数）
  • 转换函数（用来转换数据类型和值的函数）
  • 聚合函数（用来进行数据聚合的函数）

大家并不需要一次全部记住，只需要知道有这样的函数就可以了，实际应用时可以查阅参考文档。

算术函数

算术运算符：+,-,*,/
为了学习算术函数，我们首先创建一张示例用表（SampleMath）。

-- DDL ：创建表
CREATE TABLE SampleMath
(m NUMERIC (10,3),
n INTEGER,
p INTEGER);

/*SQL Server PostgreSQL*/
-- DML ：插入数据
BEGIN TRANSACTION;
INSERT INTO SampleMath(m, n, p) VALUES (500, 0, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (-180, 0, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, NULL, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 7, 3);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 5, 2);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 4, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (8, NULL, 3);
INSERT INTO SampleMath(m, n, p) VALUES (2.27, 1, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (5.555,2, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 1, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (8.76, NULL, NULL);
COMMIT;

NUMERIC是大多数DBMS都支持的一种数据类型,通过NUMBERIC(全体位数,小数位数 )的形式来指定数值的大小。

ABS,绝对值:

-- 计算数值的绝对值
SELECT m,
ABS(m) AS abs_col
FROM SampleMath;

image
通过上述结果我们可以发现，ABS函数的参数为NULL时，结果也是NULL。并非只有ABS函数如此，其实绝大多数函数对于NULL都返回NULL(但是转换函数中的COALESCE函数除外)。

MOD,求余:
因为小数计算中并没有余数的概念，所以只能对整数类型的列使用MOD函数。

-- 计算除法（n ÷ p）的余数
SELECT n, p,
MOD(n, p) AS mod_col
FROM SampleMath;

image

ROUND,四舍五入:

-- 对m列的数值进行n列位数的四舍五入处理
SELECT m, n,
ROUND(m, n) AS round_col
FROM SampleMath;

image

字符串函数

为了学习字符串函数，我们再来创建一张表（SampleStr），

-- DDL ：创建表
CREATE TABLE SampleStr
(str1 VARCHAR(40),
str2 VARCHAR(40),
str3 VARCHAR(40);
-- SQL Server PostgreSQL
-- DML ：插入数据
BEGIN TRANSACTION;
INSERT INTO SampleStr (str1, str2, str3) VALUES ('opx' ,
'rt',NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc' ,
'def' ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('山田' ,
'太郎' ,'是我');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aaa' ,
NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES (NULL ,
'xyz',NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('@!#$%' ,
NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('ABC' ,
NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aBC' ,
NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc太郎' ,
'abc' ,'ABC');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abcdefabc' ,
'abc' ,'ABC');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('micmic' ,
'i' ,'I');
COMMIT;

image

||,拼接:

-- postgresql
SELECT str1, str2,
str1 | | str2 AS str_concat
FROM SampleStr;

image
进行字符串拼接时，如果其中包含 NULL，那么得到的结果也是NULL。这是因为“||”也是变了形的函数。当然，三个以上的字符串也可以进行拼接.

-- 拼接三个字符串（str1+str2+str3）
-- Oracle DB2 PostgreSQL
SELECT str1, str2, str3,
str1 | | str2 | | str3 AS str_concat
FROM SampleStr
WHERE str1 = '山田';

image
这里也有一点需要大家注意， || 函数在 SQL Server 和 MySQL 中无法使用。MySQL使用CONCAT函数来完成字符串的拼接。

-- MySQL
SELECT str1, str2, str3,
CONCAT(str1, str2, str3) AS str_concat
FROM SampleStr;

LENGTH,字符串长度:
想要知道字符串中包含多少个字符时，可以使用 LENGTH（长度）函数.

-- 计算字符串长度
SELECT str1,
LENGTH(str1) AS len_str
FROM SampleStr;

image
与半角英文字母占用1字节不同，汉字这样的全角字符会占用2个以上的字节（称为多字节字符）。因此，使用MySQL中的LENGTH这样以字节为单位的函数进行计算时，“LENGTH( 山田 )”的返回结果是4。同样是LENGTH函数，不同DBMS的执行结果也不尽相同。MySQL中还存在计算字符串长度的自有函数CHAR_LENGTH.

LOWER,小写转换:
LOWER函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。因此,该函数并不适用于英文字母以外的场合。此外，该函数并不影响原本就是小写的字符。

SELECT str1,
LOWER(str1) AS low_str
FROM SampleStr
WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');

image
UPPER,大写转换:

SELECT str1,
UPPER(str1) AS up_str
FROM SampleStr
WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');

image

REPLACE,字符串的替换:

SELECT str1, str2, str3,
REPLACE(str1, str2, str3) AS rep_str
FROM SampleStr;

image

SUBSTRING,字符串的截取:
image
这里的起始下标应该是从1开始计数的。
需要大家注意的是，该函数也存在和LENGTH 函数同样的多字节字符的问题.

-- 截取出字符串中第3位和第4位的字符
-- PostgreSQL MySQL
SELECT str1,
SUBSTRING(str1 FROM 3 FOR 2) AS sub_str
FROM SampleStr;

image
虽然上述SUBSTRING函数的语法是标准SQL承认的正式语法，但是现在只有PostgreSQL和MySQL支持该语法。

日期函数

虽然SQL中有很多日期函数，但是其中大部分都依存于各自的DBMS，因此无法统一说明。本节将会介绍那些被标准SQL承认的可以应用于绝大多数 DBMS 的函数。
CURRENT_DATE,当前日期:
CURRENT_DATE函数能够返回SQL执行的日期，也就是该函数执行时的日期。由于没有参数，因此无需使用括号。执行日期不同，CURRENT_DATE函数的返回值也不同。如果在2009年12月13日执行该函数，会得到返回值“2009-12-13”。如果在2010年1月1日执行，就会得到返回值“2010-01-01”.

-- PostgreSQL MySQL
SELECT CURRENT_DATE;

image

CURRENT_TIME,当前时间:
CURRENT_TIME 函数能够取得SQL执行的时间，也就是该函数执行时的时间。由于该函数也没有参数，因此同样无需使用括号。

-- PostgreSQL MySQL
SELECT CURRENT_TIME;

image

CURRENT_TIMESTAMP,当前日期和时间:
CURRENT_TIMESTAMP函数具有CURRENT_DATE + CURRENT_TIME的功能。使用该函数可以同时得到当前的日期和时间，当然也可以从结果中截取日期或者时间。

-- SQL Server PostgreSQL MySQL
SELECT CURRENT_TIMESTAMP;

image

EXTRACT,截取日期元素:
image
使用EXTRACT函数可以截取出日期数据中的一部分，例如“年”,“月”，或者“小时”“秒”等。该函数的返回值并不是日期类型而是数值类型。

-- PostgreSQL MySQL
SELECT CURRENT_TIMESTAMP,
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;

image

转换函数

“转换”这个词的含义非常广泛，在 SQL 中主要有两层意思：

  • 一是数据类型的转换，简称为类型转换，在英语中称为 cast；
  • 另一层意思是值的转换。

image
进行类型转换需要使用CAST函数。之所以需要进行类型转换，

  • 是因为可能会插入与表中数据类型不匹配的数据，
  • 或者在进行运算时由于数据类型不一致发生了错误，
  • 又或者是进行自动类型转换会造成处理速度低下。

这些时候都需要事前进行数据类型转换.

-- 将字符串类型转换为数值类型
-- PostgreSQL
SELECT CAST('0001' AS INTEGER) AS int_col;
-- MySQL
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;

image

-- 将字符串类型转换为日期类型
-- PostgreSQL MySQL
SELECT CAST('2009-12-14' AS DATE) AS date_col;

image

COALESCE,将NULL转换为其他值:
image
COALESCE是SQL特有的函数.该函数会返回可变参数中左侧开始第1个不是NULL的值。参数个数是可变的，因此可以根据需要无限增加。其实转换函数的使用还是非常频繁的。在SQL语句中将NULL转换为其他值时就会用到转换函数.就像之前我们学习的那样,运算或者函数中含有NULL时,结果全都会变为NULL。能够避免这种结果的函数就是 COALESCE。

-- 将NULL转换为其他值
-- PostgreSQL MySQL
SELECT COALESCE(NULL, 1) AS col_1,
COALESCE(NULL, 'test', NULL) AS col_2,
COALESCE(NULL, NULL, '2009-11-01') AS col_3;

image

-- 使用SampleStr表中的列作为例子
SELECT COALESCE(str2, 'NULL')
FROM SampleStr;

image
这样，即使包含NULL的列，也可以通过COALESCE函数转换为其他值之后再应用到函数或者运算当中，这样结果就不再是NULL了。
此外，多数DBMS中都提供了特有的COALESCE的简化版函数（如Oracle 中的 NVL 等），但由于这些函数都依存于各自的DBMS，因此还是推荐大家使用通用的COALESCE函数。

谓词

学习重点

  • 谓词就是返回值为真值的函数。
  • 掌握LIKE的三种使用方法（前方一致、中间一致、后方一致）。
  • 需要注意BETWEEN包含三个参数。
  • 想要取得NULL数据时必须使用IS NULL。
  • 可以将子查询作为IN和EXISTS的参数。

什么是谓词

谓词(predicate),虽然之前我们没有提及谓词这个该念，但其实大家已经使用过了。例如, =、 <、 >、 <> 等比较运算符，其正式的名称就是比较谓词。通俗来讲谓词是函数中的一种，是需要满足特定条件的函数，该条件就是返回值是真值。对通常的函数来说，返回值有可能是数字、字符串或者日期等，但是谓词的返回值全都是真值(TRUE/FALSE/UNKNOWN).这也是谓词和函数的最大区别。
本节将会介绍以下谓词。

  • LIKE
  • BETWEEN
  • IS NULL、 IS NOT NULL
  • IN
  • EXISTS

LIKE谓词——字符串的部分一致查询

截至目前，我们使用字符串作为查询条件的例子中使用的都是 =。这里的 = 只有在字符串完全一致时才为真。与之相反，LIKE谓词更加模糊一些，当需要进行字符串的部分一致查询时需要使用该谓词。部分一致大体可以分为前方一致、中间一致和后方一致三种类型。
首先我们来创建一张表 6-1 那样的只有 1 列的表。
image

-- 创建SampleLike表
-- DDL ：创建表
CREATE TABLE SampleLike
( strcol VARCHAR(6) NOT NULL,
PRIMARY KEY (strcol));
-- PostgreSQL
-- DML ：插入数据
BEGIN TRANSACTION;
INSERT INTO SampleLike (strcol) VALUES ('abcddd');
INSERT INTO SampleLike (strcol) VALUES ('dddabc');
INSERT INTO SampleLike (strcol) VALUES ('abdddc');
INSERT INTO SampleLike (strcol) VALUES ('abcdd');
INSERT INTO SampleLike (strcol) VALUES ('ddabc');
INSERT INTO SampleLike (strcol) VALUES ('abddc');
COMMIT;

-- 使用LIKE进行前方一致查询
SELECT *
FROM SampleLike
WHERE strcol LIKE 'ddd%';

image
其中的%是代表“0字符以上的任意字符串”的特殊符号，本例中代表“以 ddd 开头的所有字符串”。

-- 使用LIKE进行中间一致查询
SELECT *
FROM SampleLike
WHERE strcol LIKE '%ddd%';

image

-- 使用LIKE进行后方一致查询
SELECT *
FROM SampleLike
WHERE strcol LIKE '%ddd';

image

此外，我们还可以使用 _（下划线），它代表了“任意 1 个字符”。

SELECT *
FROM SampleLike
WHERE strcol LIKE 'abc__';

image

-- 查询“abc+任意3个字符”的字符串
SELECT *
FROM SampleLike
WHERE strcol LIKE 'abc___';

image

BETWEEN谓词——范围查询

-- 选取销售单价为100～1000日元的商品
SELECT product_name, sale_price
FROM Product
WHERE sale_price BETWEEN 100 AND 1000;

image
BETWEEN 的特点就是结果中会包含 100 和 1000 这两个临界值。如果不想让结果中包含临界值，那就必须使用 < 和 >.

-- 选取出销售单价为101 ～999日元的商品
SELECT product_name, sale_price
FROM Product
WHERE sale_price > 100
AND sale_price < 1000;

image

IS NULL、 IS NOT NULL——判断是否为NULL

-- 选取出进货单价（purchase_price）为NULL的商品
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price IS NULL;

image

-- 选取进货单价（purchase_price）不为NULL的商品
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price IS NOT NULL;

image

IN谓词——OR的简便用法

-- 通过OR指定多个进货单价进行查询
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price = 320
OR purchase_price = 500
OR purchase_price = 5000;

image

-- 通过IN来指定多个进货单价进行查询
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price IN (320, 500, 5000);

-- 使用NOT IN进行查询时指定多个排除的进货单价进行查询
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price NOT IN (320, 500, 5000);

image
但需要注意的是，在使用IN 和 NOT IN 时是无法选取出 NULL 数据的。实际结果也是如此，上述两组结果中都不包含进货单价为 NULL 的叉子和圆珠笔。 NULL 终究还是需要使用IS NULL 和 IS NOT NULL 来进行判断。

使用子查询作为IN谓词的参数

IN 谓词（NOT IN 谓词）具有其他谓词所没有的用法，那就是可以使用子查询作为其参数。子查询就是SQL内部生成的表，因此也可以说“能够将表作为IN的参数”。同理，我们还可以说“能够将视图作为 IN 的参数”.
为了掌握详细的使用方法,让我们再添加一张新表.下面我们来创建表6-2 ShopProduct(商店商品），显示出哪些商店销售哪些商品。
image

-- 创建ShopProduct（商店商品）表的CREATE TABLE语句
CREATE TABLE ShopProduct
(shop_id CHAR(4) NOT NULL,
shop_name VARCHAR(200) NOT NULL,
product_id CHAR(4) NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY (shop_id, product_id));

该CREATE TABLE 语句的特点是指定了 2 列作为主键（primary key）。这样做当然还是为了区分表中每一行数据，由于单独使用商店编号（shop_id）或者商品编号（product_id不能满足要求，因此需要对商店和商品进行组合。

-- 向ShopProduct表中插入数据的INSERT语句
-- PostgreSQL
BEGIN TRANSACTION;
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0001', 30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0002', 50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0003', 15);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002', 30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003', 120);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004', 20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006', 10);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007', 40);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0003', 20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0004', 50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0006', 90);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0007', 70);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000D', '福冈', '0001', 100);
COMMIT;

-- 使用子查询作为IN的参数
-- 取得“在大阪店销售的商品的销售单价”
SELECT product_name, sale_price
FROM Product
WHERE product_id IN (SELECT product_id
FROM ShopProduct
WHERE shop_id = '000C');

image
子查询是从内层开始执行的。因此，该SELECT 语句也是从内层的子查询开始执行，然后像下面这样展开。

-- 子查询展开后的结果
SELECT product_name, sale_price
FROM Product
WHERE product_id IN ('0003', '0004', '0006', '0007');

由于各个商店销售的商品都在不断发生变化，因此 ShopProduct 表内大阪店销售的商品也会发生变化。如果 SELECT 语句中没有使用子查询的话，一旦商品发生了改变，那么 SELECT 语句也不得不进行修改，而且这样的修改工作会变得没完没了。反之，如果在 SELECT 语句中使用了子查询，那么即使数据发生了变更，还可以继续使用同样的 SELECT 语句。这样也就减少了我们的常规作业（单纯的重复操作）。
像这样可以完美应对数据变更的程序称为“易维护程序”，或者“免维护程序”。这也是系统开发中需要重点考虑的部分。希望大家在开始学习编程时，就能够有意识地编写易于维护的代码。
IN 的否定形式 NOT IN 同样可以使用子查询作为参数，其语法也和IN 完全一样。

-- 使用子查询作为NOT IN的参数
SELECT product_name, sale_price
FROM Product
WHERE product_id NOT IN (SELECT product_id
FROM ShopProduct
WHERE shop_id = '000A');

EXIST谓词

EXIST（存在）谓词的主语是“记录”。

-- 使用EXIST选取出“大阪店在售商品的销售单价”
-- SQL Server DB2 PostgreSQL MySQL
SELECT product_name, sale_price
FROM Product AS P
WHERE EXISTS (SELECT *
FROM ShopProduct AS SP
WHERE SP.shop_id = '000C'
AND SP.product_id = P.product_id);

image
EXIST是只有1个参数的谓词。 EXIST只需要在右侧书写1个参数，该参数通常都会是一个子查询。

(SELECT *
FROM ShopProduct AS SP
WHERE SP.shop_id = '000C'
AND SP.product_id = P.product_id)

上面这样的子查询就是唯一的参数。确切地说，由于通过条件“SP.product_id = P.product_id”将Product 表和 ShopProduct表进行了联接，因此作为参数的是关联子查询。 EXIST 通常都会使用关联子查询作为参数,虽然严格来说语法上也可以使用非关联子查询作为参数，但实际应用中几乎没有这样的情况。
通常指定关联子查询作为EXIST的参数。

由于EXIST只关心记录是否存在，因此返回哪些列都没有关系。 EXIST只会判断是否存在满足子查询中WHERE子句指定的条件“商店编号(shop_id)为'000C'，商品(Product)表和商店商品（ShopProduct）表中商品编号（product_id）相同”的记录，只有存在这样的记录时才返回真（TRUE）。因此，即使写成代码清单 6-39 那样，结果也不会发生改变。
image
大家**可以把在 EXIST 的子查询中书写 SELECT * 当作 SQL 的一种习惯,作为EXIST参数的子查询中经常会使用SELECT ***。

就像 EXIST 可以用来替换 IN 一样， NOT IN 也可以用 NOT EXIST来替换。

-- 使用NOT EXIST读取出“东京店在售之外的商品的销售单价”
-- SQL Server DB2 PostgreSQL MySQL
SELECT product_name, sale_price
FROM Product AS P
WHERE NOT EXISTS (SELECT *
FROM ShopProduct AS SP
WHERE SP.shop_id = '000A'
AND SP.product_id = P.product_id);

image

CASE表达式

学习重点

  • CASE表达式分为简单CASE表达式和搜索CASE表达式两种。搜索CASE表达式包含简单CASE表达式的全部功能。
  • 虽然CASE表达式中的ELSE子句可以省略,但为了让SQL语句更加容易理解，还是希望大家不要省略。
  • CASE表达式中的END不能省略。
  • 使用CASE表达式能够将SELECT语句的结果进行组合。
  • 虽然有些DBMS提供了各自特有的CASE表达式的简化函数，例如Oracle中的DECODE和MySQL中的IF，等等，但由于它们并非通用的函数，功能上也有些限制，因此有些场合无法使用。

什么是CASE表达式

CASE表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。在 C语言和Java等流行的编程语言中，通常都会使用IF 语句或者CASE语句。CASE表达式就是这些语句的SQL版本。

CASE表达式的语法

CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。但是，由于搜索 CASE 表达式包含了简单 CASE 表达式的全部功能，因此本节只会介绍搜索CASE表达式。
image
WHEN 子句中的“< 求值表达式 >”就是类似“列 = 值”这样，返回值为真（TRUE/FALSE/UNKNOWN）的表达式。我们也可以将其看作使用 =、 != 或者 LIKE、 BETWEEN 等谓词编写出来的表达式。
CASE 表达式会从对最初的 WHEN 子句中的“< 求值表达式 >”进行求值开始执行。所谓求值，就是要调查该表达式的真值是什么。如果结果为真（TRUE），那么就返回THEN子句中的表达式， CASE 表达式的执行到此为止。如果结果不为真，那么就跳转到下一条 WHEN 子句的求值之中。如果直到最后的WHEN子句为止返回结果都不为真，那么就会返回ELSE中的表达式，执行终止。

从CASE表达式名称中的“表达式”我们也能看出来，上述这些整体构成了一个表达式。并且由于表达式最终会返回一个值，因此 CASE 表达式在 SQL 语句执行时，也会转化为一个值。虽然使用分支众多的 CASE表达式编写几十行代码的情况也并不少见，但是无论多么庞大的 CASE 表达式，最后也只会返回类似“1”或者“' 渡边先生 '”这样简单的值。

CASE表达式的使用方法

-- 通过CASE表达式将A ～ C的字符串加入到商品种类当中
SELECT product_name,
CASE WHEN product_type = '衣服'
THEN 'A ：' | | product_type
WHEN product_type = '办公用品'
THEN 'B ：' | | product_type
WHEN product_type = '厨房用具'
THEN 'C ：' | | product_type
ELSE NULL
END AS abc_product_type
FROM Product;

image
ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL。但为了防止有人漏读，还是希望大家能够显示地写出ELSE子句.
此外， CASE表达式最后的“END”是不能省略的，请大家特别注意不要遗漏。

CASE 表达式的便利之处就在于它是一个表达式。之所以这么说，是因为表达式可以书写在任意位置，也就是像“1 + 1”这样写在什么位置都可以的意思。

-- 通常使用GROUP BY也无法实现行列转换
SELECT product_type,
SUM(sale_price) AS sum_price
FROM Product
GROUP BY product_type;

image

-- 使用CASE表达式进行行列转换
-- 对按照商品种类计算出的销售单价合计值进行行列转换
SELECT SUM(CASE WHEN product_type = '衣服'
THEN sale_price ELSE 0 END) AS sum_price_clothes,
SUM(CASE WHEN product_type = '厨房用具'
THEN sale_price ELSE 0 END) AS sum_price_kitchen,
SUM(CASE WHEN product_type = '办公用品'
THEN sale_price ELSE 0 END) AS sum_price_office
FROM Product;

image
在对 SELECT 语句的结果进行编辑时， CASE 表达式能够发挥较大作用.

image
image
image

练习题

image
image
不仅是IN，通常的谓词都无法与NULL进行比较，因此进货单价（purchase_price）为NULL的叉子和圆珠笔都没有出现在结果之中.
image
其实这是SQL中最危险的陷阱。NOT IN的参数中包含NULL时结果通常会为空，也就是无法选取出任何记录。为什么会得到这样的结果呢？其中的理由十分复杂，属于中级学习的范畴，因此本书中不会详细介绍。这里希望大家了解的是NOT IN的参数中不能包含NULL。不仅仅是指定NULL的情况，使用子查询作为NOT IN 的参数时，该子查询的返回值也不能是 NULL。请大家一定要遵守这一规定。

image

SELECT SUM(CASE WHEN sale_price <= 1000 
 THEN 1 ELSE 0 END) AS low_price,
 SUM(CASE WHEN sale_price BETWEEN 1001 AND 3000 
 THEN 1 ELSE 0 END) AS mid_price,
 SUM(CASE WHEN sale_price >= 3001 
 THEN 1 ELSE 0 END) AS high_price
 FROM Product;

posted on 2022-10-29 17:01  朴素贝叶斯  阅读(91)  评论(0)  编辑  收藏  举报

导航