第 1 章 数据库和 SQL
SQL 概要
根据对 RDBMS 赋予的指令种类的不同, SQL 语句可以分为以下三类
- DDL
DDL(Data Definition Language,数据定义语言)用来创建或者删除存储数据用的数据库以及数据库中的表等对象。 DDL 包含以下几种指令。
CREATE: 创建数据库和表等对象
DROP: 删除数据库和表等对象
ALTER: 修改数据库和表等对象的结构
- DML
DML(Data Manipulation Language,数据操纵语言)用来查询或者变更表中的记录。 DML 包含以下几种指令。
SELECT:查询表中的数据
INSERT:向表中插入新数据
UPDATE:更新表中的数据
DELETE:删除表中的数据
- DCL
DCL(Data Control Language,数据控制语言)用来确认或者取消对数据库中的数据进行的变更。除此之外,还可以对 RDBMS 的用户是否有权限操作数据库中的对象(数据库表等)进行设定。 DCL 包含以下几种指令。
COMMIT: 确认对数据库中的数据进行的变更
ROLLBACK: 取消对数据库中的数据进行的变更
GRANT: 赋予用户操作权限
REVOKE: 取消用户的操作权限
SQL语句以分号(;)结尾
字符串和日期常数需要使用单引号(’)括起来
数字常数无需加注单引号(直接书写数字即可)
DDL 语句使用
# 创建表和各个列,以及创建对应的约束 NOT NULL & PRIMARY KEY
# PRIMARY KEY 的对象记得带括号()
CREATE TABLE Addressbook
(regist_no INTEGER NOT NULL,
name VARCHAR(128) NOT NULL,
address VARCHAR(256) NOT NULL,
tel_no CHAR(10) ,
mail_address CHAR(20) ,
PRIMARY KEY (regist_no));
ALTER TABLE Addressbook ADD COLUMN postal_code CHAR(8) NOT NULL; # 添加一列和对应约束
DROP TALBE Addressbook; # 删除表
第 2 章 查询基础
SELECT 语句基础
SQL 中子句的书写顺序是固定的,不能随意更改。 WHERE 子句必须紧跟在 FROM 子句之后
-- 一行注释
/* 多行注释 */
请写出一条 SELECT 语句,从 Product 表中选取出满足“销售单价打九折之后利润高于 100 日元的办公用品和厨房用具”条件的记录。查询结果要包括 product_name 列、 product_type 列以及销售单价打九折之后的利润(别名设定为 profit)。
SELECT product_name, product_type,
sale_price * 0.9 - purchase_price AS profit -- 增加一列写法
FROM Product
WHERE sale_price * 0.9 - purchase_price > 100
AND ( product_type = '办公用品'
OR product_type = '厨房用具');
第 3 章 聚合与排序
聚合函数
COUNT
、SUM
、AVG
、MAX
、MIN
聚合函数会将 NULL 排除在外。但 COUNT(*)
例外,并不会排除 NULL。COUNT 函数的结果根据参数的不同而不同。 COUNT(*)
会得到包含 NULL 的数据行数,而 COUNT(<列名>)
会得到 NULL 之外的数据行数。
MAX/MIN
函数几乎适用于所有数据类型的列。 SUM/AVG
函数只适用于数值类型的列。
想要计算值的种类时,可以在 COUNT 函数的参数中使用 DISTINCT
。
SELECT COUNT(*), COUNT(col_1) FROM NullTbl; -- 3, 0
SELECT DISTINCT COUNT(product_type) FROM Product; -- 值的种类
GROUP BY
SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
GROUP BY <列名1>, <列名2>, <列名3>, ……;
使用聚合函数和 GROUP BY 子句时需要注意以下4点。
① 只能写在 SELECT 子句之中(MySQL 可以)
SELECT product_name, purchase_price, COUNT(*)
FROM Product
GROUP BY purchase_price;
② GROUP BY 子句中不能使用 SELECT 子句中列的别名(MySQL 可以)
SELECT product_type AS pt, COUNT(*)
FROM Product
GROUP BY pt;
③ GROUP BY 子句的聚合结果是无序的
④ WHERE 子句中不能使用聚合函数
SELECT product_type, COUNT(*) -- 只有 SELECT 子句和 HAVING 子句(以及 ORDER BY 子句)中能够使用聚合函数
FROM Product
WHERE COUNT(*) = 2 -- 不能在 WHERE 子句中使用聚合
GROUP BY product_type;
HAVING
WHERE 子句只能指定记录(行)的条件,而不能用来指定组的条件(例如,“数据行数为 2 行”或者“平均值为 500”等)
WHERE 子句 = 指定行所对应的条件
HAVING 子句 = 指定组所对应的条件
聚合键所对应的条件不应该书写在 HAVING 子句当中,而应该书写在 WHERE 子句当中。
ORDER BY
子句书写顺序 SELECT 子句 → FROM 子句 → WHERE 子句 → GROUP BY 子句 →HAVING 子句 → ORDER BY 子句
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price, product_id; -- 指定多个排序键
排序键中包含 NULL 时,会在开头或末尾进行汇总
在 GROUP BY 子句中不能使用 SELECT 子句中定义的别名,但是在 ORDER BY 子句中却是允许使用别名的。因为 SELECT 子句的执行顺序在 GROUP BY 子句之后, ORDER BY 子句之前。
ORDER BY 子句中也可以使用存在于表中、但并不包含在 SELECT 子句之中的列
第 4 章 数据更新
INSERT
INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);
INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (NULL, 值2, 值3, ……); -- 插入 NULL
INSERT INTO <表名> VALUES (值1, 值2, 值3, ……); -- 全列插入的话
INSERT INTO <表名> VALUES (值1, 值2, 值3, ……), (值1, 值2, 值3, ……); -- 多行插入
// 从其他表中复制数据
INSERT INTO ProductCopy (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date
FROM Product;
我们还可以向表中插入默认值(初始值)。可以通过在创建表的 CREATE TABLE 语句中设置 DEFAULT 约束来设定默认值。
省略 INSERT 语句中的列名,就会(在未给该列赋值时)自动设定为该列的默认值(没有默认值时会设定为 NULL)。
INSERT 语句的 SELECT 语句中,可以使用 WHERE 子句或者 GROUP BY 子句等任何 SQL 语法(但使用 ORDER BY 子句并不会产生任何效果)
DELETE
删除数据
① DROP TABLE 语句可以将表完全删除
② DELETE 语句会留下表(容器),而删除表中的全部数据
DELETE FROM <表名>; -- 删除全表数据
DELETE FROM <表名> WHERE <条件>; -- 删除指定条件数据
因为 DELETE 语句的对象是行而不是列,所以 DELETE 语句无法只删除部分列的数据。
DELETE * FROM Product;
错误
与 SELECT 语句不同的是, DELETE 语句中不能使用 GROUP BY、HAVING 和 ORDER BY 三类子句,而只能使用WHERE 子句。
TRUNCATE <表名>; -- TRUNCATE 只能删除表中的全部数据,但是执行速度快
UPDATE
UPDATE <表名> SET <列名> = <表达式>;
-- 多列更新
UPDATE Product
SET sale_price = sale_price * 10,
purchase_price = purchase_price / 2
WHERE product_type = '厨房用具';
使用 UPDATE 语句可以将值清空为 NULL(但只限于未设置 NOT NULL 约束的列)
事务
事务就是需要在同一个处理单元中执行的一系列更新处理的集合。
-- 创建事务
事务开始语句; START TRANSACTION;
DML语句①;
DML语句②;
DML语句③;
事务结束语句(COMMIT或者ROLLBACK); COMMIT;
COMMIT 是提交事务包含的全部更新处理的结束指令,相当于文件处理中的覆盖保存。一旦提交,就无法恢复到事务开始前的状态了。
默认使用自动提交模式的 DBMS 有 SQL Server、 PostgreSQL 和 MySQL 等,该模式下每一条语句都括在事务的开始语句和结束语句之中。
ACID 特性
- 原子性(Atomicity)
原子性是指在事务结束时,其中所包含的更新处理要么全部执行,要么完全不执行
- 一致性(Consistency)
一致性指的是事务中包含的处理要满足数据库提前设置的约束,如主键约束或者 NOT NULL 约束等。
- 隔离性(Isolation)
隔离性指的是保证不同事务之间互不干扰的特性。即使某个事务向表中添加了记录,在没有提交之前,其他事务也是看不到新添加的记录的。
- 持久性(Durability)
持久性也可以称为耐久性,指的是在事务(不论是提交还是回滚)结束后, DBMS 能够保证该时间点的数据状态会被保存的特性。即使由于系统故障导致数据丢失,数据库也一定能通过某种手段进行恢复。
保证持久性
的方法根据实现的不同而不同,其中最常见的就是将事务的执行记录保存到硬盘等存储介质中(该执行记录称为日志
)。当发生故障时,可以通过日志恢复到故障发生前的状态。
第 5 章 复杂查询
视图和表
表
中存储的是实际数据,而视图
中保存的是从表中取出数据所使用的 SELECT 语句。
视图的优点
- 视图无需保存数据,因此可以节省存储设备的容量
- 可以将频繁使用的 SELECT 语句保存成视图,这样就不用每次都重新书写了
视图的创建
CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……) AS <SELECT语句> -- AS 别忘了!
CREATE VIEW ViewPractice5_1 AS
SELECT product_name, sale_price, regist_date
FROM Product
WHERE sale_price >= 1000
AND regist_date = '2009-09-20';
视图的限制
- 定义视图时不能使用ORDER BY子句
视图和表一样, 数据行都是没有顺序的。
- 对视图进行更新有严格限制
① SELECT 子句中未使用 DISTINCT
② FROM 子句中只有一张表
③ 未使用 GROUP BY 子句
④ 未使用 HAVING 子句
视图和表需要同时进行更新,因此通过汇总得到的视图无法进行更新。
删除视图
DROP VIEW 视图名称(<视图列名1>, <视图列名2>, ……)
子查询
子查询就是一次性视图(SELECT 语句)。与视图不同,子查询在 SELECT 语句执行完毕之后就会消失。
子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中
-- 创建的是视图
CREATE VIEW ProductSum (product_type, cnt_product) AS
SELECT product_type, COUNT(*) FROM Product GROUP BY product_type; -- 这条 SELECT 语句
-- 返回的是表
SELECT product_type, cnt_product FROM
(SELECT product_type, COUNT(*) AS cnt_product FROM Product GROUP BY product_type) -- 在这里
AS ProductSum;
标量子查询
标量子查询就是返回单一值的子查询
SELECT product_id, product_name, sale_price FROM Product
-- WHERE 子句不能使用聚合函数,使用标量子查询
WHERE sale_price > (SELECT AVG(sale_price) FROM Product);
标量子查询的书写位置并不仅仅局限于 WHERE 子句中,通常任何可以使用单一值的位置都可以使用。也就是说, 能够使用常数或者列名的地方,无论是 SELECT 子句、 GROUP BY 子句、 HAVING 子句,还是 ORDER BY 子句,几乎所有的地方都可以使用。
-- SELECT 子句
SELECT product_id, product_name, sale_price,
(SELECT AVG(sale_price) FROM Product) AS avg_price FROM Product;
-- HAVING 子句
SELECT product_type, AVG(sale_price) FROM Product GROUP BY product_type
HAVING AVG(sale_price) > (SELECT AVG(sale_price) FROM Product);
注意事项:
子查询绝对不能返回多行结果
关联子查询
在细分的组内进行比较时,需要使用关联子查询
-- 按照商品种类计算平均价格
SELECT AVG(sale_price) FROM Product GROUP BY product_type;
-- 找出 销售单价 > 商品种类平均价格 的商品
SELECT product_type, product_name, sale_price
FROM Product AS P1
WHERE sale_price > (SELECT AVG(sale_price) FROM Product AS P2
WHERE P1.product_type = P2.product_type -- 关键,在同一商品种类中对各商品的销售单价和平均单价进行比较
GROUP BY product_type);
-- 商品平均价格作为其中一列
SELECT product_id, product_name, product_type, sale_price,
(SELECT AVG(sale_price) FROM Product P2
WHERE P1.product_type = P2.product_type
GROUP BY P1.product_type) AS avg_sale_price
FROM Product P1; -- P1 在最后
在使用关联子查询时,需要在表所对应的列名之前加上表的别名,以 < 表名 >.< 列名 >
的形式记述。
第 6 章 各种各样的函数
函数
函数的种类
- 算术函数(用来进行数值计算的函数)
- 字符串函数(用来进行字符串操作的函数)
- 日期函数(用来进行日期操作的函数)
- 转换函数(用来转换数据类型和值的函数)
- 聚合函数(用来进行数据聚合的函数)
算数函数
SELECT m, func(m) AS func_col FROM SampleMath;
ABS(数值)
MOD(被除数,除数) -- 被除数 % 除数
ROUND(对象数值,保留小数的位数)
字符串函数
-- MySQL 无法使用 字符串1||字符串2
SELECT str1, str2, str1 || str2 AS str_concat FROM SampleStr; -- 字符串拼接
SELECT str1, str2, CONCAT(str1, str2) AS str_concat FROM SampleStr; -- MySQL 用法
SELECT str1,LENGTH(str1) AS len_str FROM SampleStr; -- LENGTH(字符串)
SELECT str1, LOWER(str1) AS low_str; -- LOWER(字符串)
SELECT str1, UPPER(str1) AS upper_str; -- UPPER(字符串)
SELECT str1, str2, str3, REPLACE(str1, str2, str3) AS rep_str FROM SampleStr; -- REPLACE(对象字符串,替换前的字符串,替换后的字符串)
SELECT str1, SUBSTRING(str1 FROM m FOR n) AS sub_str FROM SampleStr; -- 从第 m 个字符开始,截取 n 个
日期函数
SELECT CURRENT_TIMESTAMP, EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year;
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP -- CURRENT_DATE + CURRENT_TIME
EXTRACT(日期元素 FROM 日期)
转换函数
“转换”在 SQL 中主要有两层意思
- 类型转换,在英语中称为 cast
-- CAST(转换前的值 AS 想要转换的数据类型)
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
SELECT CAST('2009-12-14' AS DATE) AS date_col;
- 值的转换
用来给某一列 NULL 赋值 COALESCE(SP.shop_id, '不确定') AS shop_id,
-- COALESCE 是 SQL 特有的函数。该函数会返回可变参数中左侧开始第 1 个不是 NULL 的值
SELECT COALESCE(NULL, 1) FROM DUAL; -- 返回 1
谓词
谓词的返回值全都是真值( TRUE/FALSE/UNKNOWN)
- LIKE
LIKE 类似正则表达式匹配,部分一致大体可以分为前方一致、中间一致和后方一致三种类型。
SELECT * FROM SampleLike WHERE strcol LIKE 'ddd%'; -- % 匹配 0-n 个
SELECT * FROM SampleLike WHERE strcol LIKE 'abc__'; -- _ 匹配 1 个
- BETWEEN
-- BETWEEN 的特点就是结果中会包含 100 和 1000 这两个临界值。如果不想让结果中包含临界值,那就必须使用 < 和 >
SELECT product_name, sale_price FROM Product WHERE sale_price BETWEEN 100 AND 1000;
- IS NULL、IS NOT NULL
SELECT product_name, purchase_price FROM Product WHERE purchase_price IS NULL;
- IN、NOT IN
SELECT product_name, purchase_price FROM Product WHERE purchase_price IN (320, 500, 5000);
使用子查询作为IN谓词的参数
SELECT product_name, sale_price FROM Product WHERE product_id IN
(SELECT product_id FROM ShopProduct WHERE shop_id = '000C'); -- 子查询
SELECT product_name, sale_price FROM Product WHERE product_id IN
('0003', '0004', '0006', '0007'); -- 子查询的等价展开
- EXIST
① EXIST 的使用方法与之前的都不相同
② 语法理解起来比较困难
③ 实际上即使不使用 EXIST,基本上也都可以使用 IN(或者 NOT IN)来代替
-- 等价于上面 IN 的查询语句
SELECT product_name, sale_price
FROM Product AS P
WHERE EXISTS -- 通常指定关联子查询作为 EXIST 的参数
(SELECT * -- 作为EXIST参数的子查询中经常会使用 SELECT *
FROM ShopProduct AS SP
WHERE SP.shop_id = '000C'
AND SP.product_id = P.product_id);
CASE 表达式
CASE 表达式是在区分情况时使用的,这种情况的区分在编程中通常称为(条件)分支
类似 swith case 语句
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
ELSE <表达式>
END
SELECT
COUNT(CASE WHEN sale_price <= 1000 THEN product_name ELSE NULL END) AS low_price,
COUNT(CASE WHEN sale_price BETWEEN 1001 AND 3000 THEN product_name ELSE null END) AS mid_price,
COUNT(CASE WHEN sale_price > 3000 THEN product_name ELSE null END) AS high_price
FROM Product;
第 7 章 集合运算
4 个集合运算符
- UNION(并集)
- EXCEPT(差集)
- INTERSECT(交集)
- CROSS JOIN(笛卡儿积)
表的加减法
集合运算注意事项
- 作为运算对象的记录的列数必须相同(2 列和 3 列的不能进行集合运算)
- 作为运算对象的记录中列的类型必须一致(MySQL 可以隐式类型转换或者使用 CAST)
- 可以使用任何 SELECT 语句,但 ORDER BY 子句只能在最后使用一次
表的加法——UNION(并集)
UNION 集合运算符会除去重复的记录,UNION ALL 可以保留重复行
SELECT product_id, product_name
FROM Product
UNION -- UNION ALL 可以保留重复行
SELECT product_id, product_name
FROM Product2
ORDER BY product_id;
选取表中公共部分——INTERSECT(MySQL 不支持)
记录的减法——EXCEPT(MySQL 不支持)
联结
集合运算的特征就是 以行方向
为单位进行操作。通俗地说,就是进行这些集合运算时,会导致记录行数的增减。
联结(JOIN)运算,简单来说,就是将其他表中的列添加过来,进行 添加列
的运算
内联结——INNER JOIN
使用联结时 SELECT 子句中的列需要按照“ <表的别名>.<列名>”的格式进行书写
进行联结时需要在 FROM 子句中使用多张表
进行内联结时必须使用 ON 子句,并且要书写在 FROM 和 WHERE 之间
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price -- table.col_name
FROM ShopProduct AS SP INNER JOIN Product AS P -- table INNER JOIN table
ON SP.product_id = P.product_id; -- ON 联结键
WHERE SP.shop_id = '000A';
外联结——OUTER JOIN
外联结通过 LEFT
和 RIGHT
指定主表
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name,
P.sale_price
FROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P -- INNER JOIN 换为 RIGHT OUTER JOIN
ON SP.product_id = P.product_id;
内联结:只包含表内信息的联结
外联结:结果中包含原表中不存在(在原表之外)的信息(设为 NULL)
在实际的业务中,例如想要生成固定行数的单据时,就需要使用外联结。
-- 3 张表的联结
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price, IP.inventory_quantity
FROM ShopProduct AS SP
INNER JOIN Product AS P ON SP.product_id = P.product_id
INNER JOIN InventoryProduct AS IP ON SP.product_id = IP.product_id
WHERE IP.inventory_id = 'P001';
交叉联结——CROSS JOIN
交叉联结运用笛卡尔积
进行交叉联结时无法使用内联结和外联结中所使用的 ON 子句,这是因为交叉联结是对两张表中的全部记录进行交叉组合,因此结果中的记录数通常是两张表中行数的乘积。
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name
FROM ShopProduct AS SP CROSS JOIN Product AS P;
第 8 章 SQL 高级处理
窗口函数
窗口函数也称为 OLAP 函数(OnLine Analytical Processing),MySQL 5.7 还是不支持该功能。
<窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <排序用列清单>)
能够作为窗口函数使用的函数
- 聚合函数:SUM、 AVG、 COUNT、 MAX、 MIN)
- 专用窗口函数:RANK、 DENSE_RANK、 ROW_NUMBER
RANK 函数
-- 增加一列 ranking,对 product_type 的商品通过 sale_price 升序排序
SELECT product_name, product_type, sale_price,
RANK () OVER (PARTITION BY product_type ORDER BY sale_price) AS ranking
FROM Product;
窗口函数兼具分组和排序两种功能。
RANK 函数:1 位、1 位、1 位、4 位
DENSE_RANK 函数:1 位、1 位、1 位、2 位
ROW_NUMBER 函数:1 位、2 位、3 位、4 位
原则上窗口函数只能在SELECT子句中使用
作为窗口函数使用的聚合函数
SELECT product_id, product_name, sale_price,
SUM (sale_price) OVER (ORDER BY product_id) AS current_sum
FROM Product;
计算移动平均
-- 指定“最靠近的 3 行”作为汇总对象
SELECT product_id, product_name, sale_price, AVG (sale_price) OVER (ORDER BY product_id
ROWS 2 PRECEDING) AS moving_avg -- 截止到之前 2 行
FROM Product;
两个 ORDER BY
OVER 子句中的 ORDER BY 只是用来决定窗口函数按照什么样的顺序进行计算的,对结果的排列顺序并没有影响
SELECT product_name, product_type, sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking
FROM Product
ORDER BY ranking; -- 根据 ranking 列升序
GROUPING 运算符
GROUPING 运算符包含以下 3 种
- ROLLUP
- CUBE
- GROUPING SETS
ROLLUP——同时得出合计和小计
一次计算出不同聚合键组合的结果
ROLLUP (< 列 1>,< 列 2>,...)
SELECT product_type, regist_date, SUM(sale_price) AS sum_price
FROM Product
-- MySQL:GROUP BY product_type, regist_date WITH ROLLUP;
GROUP BY ROLLUP(product_type, regist_date);
GROUP BY ROLLUP(product_type, regist_date)
得到下面各种结果
- GROUP BY ()
- GROUP BY (product_type)
- GROUP BY (product_type, regist_date)
ROLLUP 是“卷起”的意思,比如卷起百叶窗、窗帘卷,等等。其名称也形象地说明了该操作能够得到像从小计到合计这样,从最小的聚合级开始,聚合单位逐渐扩大的结果。
GROUPING 函数——让 NULL 更加容易分辨
为了避免混淆, SQL 提供了一个用来判断超级分组记录的 NULL 的特定函数 —— GROUPING 函数。
该函数在其参数列的值为超级分组记录所产生的 NULL 时返回 1
,其他情况返回 0
SELECT CASE WHEN GROUPING(product_type) = 1
THEN '商品种类 合计' ELSE product_type END AS product_type,
CASE WHEN GROUPING(regist_date) = 1
THEN '登记日期 合计'
-- CAST 满足 CASE 表达式所有分支的返回值必须一致的条件
ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
SUM(sale_price) AS sum_price FROM Product
GROUP BY ROLLUP(product_type, regist_date);
CUBE——用数据来搭积木
SELECT CASE WHEN GROUPING(product_type) = 1
THEN '商品种类 合计'
ELSE product_type END AS product_type,
CASE WHEN GROUPING(regist_date) = 1
THEN '登记日期 合计'
ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
SUM(sale_price) AS sum_price
FROM Product
GROUP BY CUBE(product_type, regist_date); -- 这里不一样
GROUP BY CUBE(product_type, regist_date)
得到下面的结果
- GROUP BY ()
- GROUP BY (product_type)
- GROUP BY (regist_date)
- GROUP BY (product_type, regist_date)
所谓 CUBE,就是将 GROUP BY 子句中聚合键的“所有可能的组合”的汇总结果集中到一个结果中。因此,组合的个数就是 2^n( n 是聚合键的个数)。
可以把 CUBE 理解为将使用聚合键进行切割的模块堆积成一个立方体。
GROUPING SETS——取得期望的积木
与 ROLLUP 或者 CUBE 能够得到规定的结果相对, GROUPING SETS 用于从中取出个别条件对应的不固定的结果。
-- 从中选取出将“商品种类”和“登记日期”各自作为聚合键的结果
SELECT CASE WHEN GROUPING(product_type) = 1
THEN '商品种类 合计'
ELSE product_type END AS product_type,
CASE WHEN GROUPING(regist_date) = 1
THEN '登记日期 合计'
ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
SUM(sale_price) AS sum_price
FROM Product
GROUP BY GROUPING SETS (product_type, regist_date); -- 这里不一样