《SQL基础教程》
Product表
CREATE TABLE Product
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER ,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));
插入数据
-- DML：插 入 数 据
BEGIN TRANSACTION;
INSERT INTO Product VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL,'2009-11-11');
COMMIT;
第一章 数据库与SQL
表定义的更新（ALTER TABLE语句）
ALTER TABLE <表名> ADD COLUMN <列的定义>；
添加一列可以存储100位的可变长字符串的product_name_pinyin列
-- Oracle和SQLServer中不用写`COLUMN`。
--Oracle
ALTER TABLE Product ADD (product_name_pinyin VARCHAR2(100));
--SQL Server
ALTER TABLE Product ADD product_name_pinyin VARCHAR(100);
删除列的ALTER TABLE语句
ALTER TABLE <表名> DROP COLUMN <列名>；
删除product_name_pinyin列
ALTER TABLE Product DROP COLUMN product_name_pinyin;
变更表名：
--Oracle PostgreSQL
ALTER TABLE Poduct RENAME TO Product;
--DB2
RENAME TABLE Poduct TO Product;
--SQL Server
sp_rename 'Poduct', 'Product';
--MySQL
RENAME TABLE Poduct to Product;
第三章 聚合与排序
3-1 对表进行聚合查询
计算表中数据的行数
SELECT COUNT(*)
FROM Product;
------
8
计算NULL之外的数据的行数
如果想得到purchase_price列（进货单价）中非空行数的话，
SELECT COUNT(purchase_price)
FROM Product;
------
6
第四章 数据的更新
4-1 数据的插入
从其他表复制数据
已存在表的情况下：
-- 用来插入数据的商品复制表
CREATE TABLE ProductCopy
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER ,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));
-- 将商品表中的数据复制到商品复制表中
---- 简单写法
INSERT INTO ProductCopy
SELECT *
FROM Product;
---- 正常方法
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;
不存在表的情况下（备份表操作）：
但是不保留主外键
SELECT * INTO ProductCopy20180502 FROM ProductCopy
第五章 复杂查询
5-2 子查询
标量子查询
标量就是单一的意思，在数据库之外的领域也经常使用
而标量子查询则有一个特殊的限制，那就是必须而且只能返回1行1列的结果。
标量子查询就是返回单一值的子查询。
例子：
-- 在WHERE子句中不能使用聚合函数
SELECT product_id, product＿name, sale_price
FROM Product
WHERE sale_price > AVG(sale_price); --！报错：WHERE子句中不能使用聚合函数
WHERE子句中不能使用聚合函数，因此这样的SELECT语句是错误的。
下面是正确方式：
-- “查询出销售单价高于平均销售单价的商品。”
SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > (SELECT AVG(sale_price)
FROM Product);
5-3 关联子查询
选取出各
条件：商品种类中高于该商品种类的平均销售单价的商品
1.按照商品种类计算平均价格
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);
该条件的意思就是，在同一商品种类中对各商品的销售单价和平均单价进行比较。
在对表中某一部分记录的集合进行比较时，就可以使用关联子查询。
在细分的组内进行比较时，需要使用关联子查询。
大家还记得我们用来说明GROUP BY子句的图
使用关联子查询进行切分的图示也基本相同
我们首先需要计算各个商品种类中商品的平均销售单价，由于该单价会用来和商品表中的各条记录进行比较，因此关联子查询实际只能返回1行结果。这也是关联子查询不出错的关键。
关联子查询执行时DBMS内部的执行情况
练习题：
5.1 创建出满足下述三个条件的视图（视图名称为ViewPractice5_1）。 使用Product（商品）表作为参照表，假设表中包含初始状态的 8行数据。
条件 1： 销售单价大于等于1000日元。
条件 2： 登记日期是2009年 9月 20日。
条件 3： 包含商品名称、销售单价和登记日期三列。
对该视图执行SELECT语句的结果如下所示。
SELECT * FROM ViewPractice5_1;
执行结果
| product_name | sale_price | regist_date |
|---|---|---|
| T恤衫 | 1000 | 2009-09-20 |
| 菜刀 | 3000 | 2009-09-20 |
5.2 向习题 5.1 中创建的视图 ViewPractice5_1中插入如下数据，会得到什么样的结果呢？
INSERT INTO ViewPractice5_1 VALUES ('刀子', 300, '2009-11-02');
5.3 请根据如下结果编写 SELECT语句，其中 sale_price_all列为全部商品的平均销售单价。
| product_id | product_name | product_type | sale_price | sale_price_all |
|---|---|---|---|---|
| 0001 | T恤衫 | 衣服 | 1000 | 2097.5000000000000000 |
| 0002 | 打孔器 | 办公用品 | 500 | 2097.5000000000000000 |
| 0003 | 运动T恤 | 衣服 | 4000 | 2097.5000000000000000 |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2097.5000000000000000 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 2097.5000000000000000 |
| 0006 | 叉子 | 厨房用具 | 500 | 2097.5000000000000000 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 2097.5000000000000000 |
| 0008 | 圆珠笔 | 办公用品 | 100 | 2097.5000000000000000 |
5.4 请根据习题5.1中的条件编写一条SQL语句，创建一幅包含如下数据的视图（名称为 AvgPriceByType）。
执行结果
| product_id | product_name | product_type | sale_price | avg_sale_price |
|---|---|---|---|---|
| 0001 | T恤衫 | 衣服 | 1000 | 2500.0000000000000000 |
| 0002 | 打孔器 | 办公用品 | 500 | 300.0000000000000000 |
| 0003 | 运动T恤 | 衣服 | 4000 | 2500.0000000000000000 |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2795.0000000000000000 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 2795.0000000000000000 |
| 0006 | 叉子 | 厨房用具 | 500 | 2795.0000000000000000 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 2795.0000000000000000 |
| 0008 | 圆珠笔 | 办公用品 | 100 | 300.0000000000000000 |
提示 ：其 中 的 关 键 是avg_sale_price列。与习题5.3不同，这里需要计算出的是各商品种类的平均销售单价。这与5-3节中使用关联子查询所得到的结果相同。
也就是说，该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询。
答案：
5.1
CREATE VIEW ViewPractice5_1
AS (SELECT product_name, sale_price, regist_date
FROM dbo.Product
WHERE sale_price >= 1000
AND regist_date = '2009-09-20'
)
5.2
对视图的更新归根结底是对视图所对应的表进行更新。因此，该INSERT语句实质上和下面的INSERT语句相同。
INSERT INTO Product (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
VALUES (NULL, '刀子', NULL, 300, NULL, '2009-11-02');
5.3
SELECT product_id, product_name, product_type, sale_price,
( SELECT AVG(sale_price) FROM Product ) AS 'sale_Price_all'
FROM dbo.Product;
5.4
方案一：
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 P2.product_type
) AS avg_sale_price
FROM Product P1;
方案二：
SELECT p1.product_id, p1.product_name, p1.product_type, p1.sale_price,p2.avg_sale_price
FROM dbo.Product p1
LEFT JOIN
(SELECT product_type,AVG(sale_price) AS avg_sale_price FROM dbo.Product
GROUP BY product_type) p2
ON p1.product_type = p2.product_type
第六章 函数、谓词、CASE表达式
6-1 各种函数
字符串函数
创建表
-- DDL：创 建 表
CREATE TABLE SampleStr
(str1 VARCHAR(40),
str2 VARCHAR(40),
str3 VARCHAR(40))
-- 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;
- 拼接
+或者|| LENGTH——字符串长度LOWER——小写转换REPLACE——字符串的替换
REPLACE(对象字符串，替换前的字符串，替换后的字符串)
SELECT str1, str2, str3,
REPLACE(str1, str2, str3) AS rep_str
FROM SampleStr;
SUBSTRING——字符串的截取
--（PostgreSQL/MySQL专用语法）
SUBSTRING（对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
-- sqlserver专用
SUBSTRING(对象字符串，截取的起始位置，截取的字符数)
SELECT str1,
SUBSTRING(str1, 3, 2) AS sub_str
FROM SampleStr;
UPPER——大写转换
日期函数
- 获取当前日期和时间
--SQL Server，PostgreSQL，MySQL
SELECT CURRENT_TIMESTAMP
--2018-05-03 17:21:07.763
- 获取当前日期
--PostgreSQL MySQL
SELECT CURRENT_DATE;
--2018-05-03
--SqlServer
SELECT CAST(CURRENT_TIMESTAMP AS DATE) AS CUR_DATE;
--2018-05-03
- 取得当前时间
--PostgreSQL MySQL
SELECT CURRENT_TIME;
--17:21:07.995+09
--SqlServer 使用CAST函数将CURRENT_TIMESTAMP转换为时间类型
SELECT CAST(CURRENT_TIMESTAMP AS TIME) AS CUR_TIME;
--17:21:07.7630000
- EXTRACT——截取日期元素
EXTRACT(日期元素 FROM 日期)
-- 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;
--SQL Server （使用如下的DATEPART函数）
SELECT CURRENT_TIMESTAMP,
DATEPART(YEAR , CURRENT_TIMESTAMP) AS year,
DATEPART(MONTH , CURRENT_TIMESTAMP) AS month,
DATEPART(DAY , CURRENT_TIMESTAMP) AS day,
DATEPART(HOUR , CURRENT_TIMESTAMP) AS hour,
DATEPART(MINUTE , CURRENT_TIMESTAMP) AS minute,
DATEPART(SECOND , CURRENT_TIMESTAMP) AS second;
6-2 谓词
EXIST谓词
“判断是否存在满足某种条件的记录”，如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。
EXIST（存在）谓词的主语是“记录”。
① EXIST的使用方法与之前的都不相同。
② 语法理解起来比较困难。
③ 实际上即使不使用EXIST，基本上也都可以使用IN（或者NOT IN）来代替。
例子：使用EXIST选取出“大阪店在售商品的销售单价”
--exists谓词
SELECT p.product_name, p.sale_price
FROM dbo.Product p
WHERE exists (
SELECT product_id FROM dbo.ShopProduct sp WHERE shop_id = '000C'
AND p.product_id = sp.product_id
)
-- IN谓词
SELECT product_name, sale_price
FROM Product
WHERE product_id IN (SELECT product_id
FROM ShopProduct
WHERE shop_id = '000C');
| product_name | sale_price |
|---|---|
| 叉子 | 500 |
| 运动T恤 | 4000 |
| 菜刀 | 3000 |
| 擦菜板 | 880 |
- EXIST的参数
之前我们学过的谓词，基本上都是像“列 LIKE 字符串”或者“列 BETWEEN 值1 AND 值2”这样需要指定2个以上的参数，而EXIST的左侧并没有任何参数。很奇妙吧？这是因为EXIST是只有1个参数的谓词。EXIST只需要在右侧书写1个参数，该参数通常都会是一个子查询。
(
SELECT product_id FROM dbo.ShopProduct sp WHERE shop_id = '000C'
AND p.product_id = sp.product_id
)
上面这样的子查询就是唯一的参数。确切地说，由于通过条件SP.product_id = P.product_id将Product表和ShopProduct表进行了联接，因此作为参数的是关联子查询。EXIST通常都会使用关联子查询作为参数
6-3 Case表达式
CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。
--简单 CASE 语法
CASE <表达式>
WHEN <表达式> THEN <表达式>
WHEN <表达式> THEN <表达式>
WHEN <表达式> THEN <表达式>
. . .
ELSE <表达式>
END
--搜索 CASE 语法
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
. . .
ELSE <表达式>
END
WHEN子句中的“<求值表达式>”就是类似“列 = 值”这样，我们也可以将其看作使用=、!=或者LIKE、BETWEEN等谓词编写出来的表达式。
例子：要得到如下结果
A：衣 服
B：办 公 用 品
C：厨 房 用 具
因为表中的记录并不包含“A： ”或者“B： ”这样的字符串，所以需要在SQL中进行添加。
SELECT 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
FROM dbo.Product;
--简单CASE
SELECT CASE product_type WHEN '衣服' THEN 'A:' + product_type
WHEN '办公用品' THEN 'B:' + product_type
WHEN '厨房用具' THEN 'C:' + product_type
ELSE NULL
END
FROM dbo.Product;
CASE表达式的书写位置
CASE表达式的便利之处就在于它是一个表达式。之所以这么说，是因为表达式可以书写在任意位置，也就是像“1 + 1”这样写在什么位置都可以的意思。例如，我们可以利用CASE表达式将下述SELECT语句结果中的行和列进行互换。
要实现下面的结果：
| sum_price_clothes | sum_price_kitchen | sum_price_office |
|---|---|---|
| 5000 | 11180 | 600 |
上述结果是根据商品种类计算出的销售单价的合计值，通常我们将商品种类列作为GROUP BY子句的聚合键来使用，但是这样得到的结果会以“行”的形式输出，而无法以列的形式进行排列。
SELECT product_type,
SUM(sale_price) AS sum_price
FROM Product
GROUP BY product_type;
但是结果是：
| product_type | sum_price |
|---|---|
| 衣服 | 5000 |
| 办公用品 | 600 |
| 厨房用具 | 11180 |
我们可以像代码清单6-43那样在SUM函数中使用CASE表达式来获得一个3列的结果。
-- 对按照商品种类计算出的销售单价合计值进行 行列转换
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;
练习题：
按照销售单价（sale_price）对练习 6.1中的 Product （商品）表中的商品进行如下分类。
● 低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、圆珠笔）
● 中档商品：销售单价在1001日元以上3000日元以下（菜刀）
● 高档商品：销售单价在3001日元以上（运动T恤、高压锅）
请编写出统计上述商品种类中所包含的商品数量的 SELECT语句，结果如下所示。
| low_price | mid_price | high_price |
|---|---|---|
| 5 | 1 | 2 |
答案：
SELECT
COUNT(CASE when sale_price<=1000 THEN sale_price END) AS low_price,
COUNT(CASE when sale_price>1001 AND sale_price<=3000 THEN sale_price END) AS mid_price,
COUNT(CASE when sale_price>3001 THEN sale_price END) AS high_price
FROM dbo.Product
第八章 SQL高级处理
8-1 窗口函数（OLAP函数 或 分析函数）
窗口语法
<窗口函数> OVER ([PARTITION BY <列清单>]
ORDER BY <排序用列清单>)
其中重要的关键字是PARTITION BY和ORDER BY，理解这两个关键字的作用是帮助我们理解窗口函数的关键。
① 能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN）
②RANK、DENSE_RANK、ROW_NUMBER等专用窗口函数
例子：根据不同的商品种类，按照销售单价从低到高的顺序创建排序表
| product_name | product_type | sale_price | ranking |
|---|---|---|---|
| 叉子 | 厨房用具 | 500 | 1 |
| 擦菜板 | 厨房用具 | 880 | 2 |
| 菜刀 | 厨房用具 | 3000 | 3 |
| 高压锅 | 厨房用具 | 6800 | 4 |
| T恤衫 | 衣服 | 1000 | 1 |
| 运动T恤 | 衣服 | 4000 | 2 |
| 圆珠笔 | 办公用品 | 100 | 1 |
| 打孔器 | 办公用品 | 500 | 2 |
SELECT product_name, product_type, sale_price,
RANK () OVER (PARTITION BY product_type
ORDER BY sale_price) AS ranking
FROM Product;
PARTITION BY能够设定排序的对象范围。本例中，为了按照商品种类进行排序，我们指定了product_type。
ORDER BY能够指定按照哪一列、何种顺序进行排序。为了按照销售单价的升序进行排列，我们指定了sale_price。此外，窗口函数中的ORDER BY与SELECT语句末尾的ORDER BY一样，可以通过关键字ASC/DESC来指定升序和降序。
窗口函数兼具分组和排序两种功能。
通过 PARTITION BY分组后的记录集合称为窗口。此处的窗口并非“窗户”的意思，而是代表范围。这也是“窗口函数”名称的由来。
无需指定PARTITION BY
SELECT product_name, product_type, sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking
FROM Product;
| product_name | product_type | sale_price | ranking |
|---|---|---|---|
| 圆珠笔 | 办公用品 | 100 | 1 |
| 叉子 | 厨房用具 | 500 | 2 |
| 打孔器 | 办公用品 | 500 | 2 |
| 擦菜板 | 厨房用具 | 880 | 4 |
| T恤衫 | 衣服 | 1000 | 5 |
| 菜刀 | 厨房用具 | 3000 | 6 |
| 运动T恤 | 衣服 | 4000 | 7 |
| 高压锅 | 厨房用具 | 6800 | 8 |
专用窗口函数的种类
- RANK函数
计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
例）有 3条记录排在第 1位时：1位、1位、1位、4位…… - DENSE_RANK函数
同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
例）有 3条记录排在第 1位时：1位、1位、1位、2位…… - ROW_NUMBER函数
赋予唯一的连续位次。
例）有 3条记录排在第 1位时：1位、2位、3位、4位……
SELECT product_name, product_type, sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking,
DENSE_RANK () OVER (ORDER BY sale_price) AS dense_ranking,
ROW_NUMBER () OVER (ORDER BY sale_price) AS row_num
FROM Product;
作为窗口函数使用的聚合函数
- 将SUM函数作为窗口函数使用
SELECT product_id, product_name, sale_price,
SUM(sale_price) OVER (ORDER BY product_id) AS current_sum
FROM Product;
计算该合计值的逻辑就像金字塔堆积那样，一行一行逐渐添加计算对象。在按照时间序列的顺序，计算各个时间的销售额总额等的时候，通常都会使用这种称为累计的统计方法。
- 将AVG函数作为窗口函数使用
SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id) AS current_avg
FROM Product;
计算移动平均
窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。其实其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架。
需要在ORDER BY子句之后使用指定范围的关键字。
--指定“最靠近的3行”作为汇总对象
SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id
ROWS 2 PRECEDING) AS moving_avg
FROM Product;
这里我们使用了ROWS（“行”）和PRECEDING（“之前”）两个关键字，将框架指定为“截止到之前~行 ”， 因 此 “ROWS 2 PRECEDING”就是将框架指定为“截止到之前2行”，也就是将作为汇总对象的记录限定为如下的“最靠近的3行 ”
这样的统计方法称为移动平均（moving average）。由于这种方法在希望实时把握“最近状态”时非常方便，因此常常会应用在对股市趋势的实时跟踪当中。
使用关键字FOLLOWING（“ 之 后 ”） 替 换PRECEDING，就可以指定“截止到之后~行”作为框架了（图8-3）。
8-2 GROUPING运算符
同时得到合计行
使用GROUP BY无法得到合计行
SELECT product_type, SUM(sale_price)
FROM Product
GROUP BY product_type;
| product_type | sum |
|---|---|
| 衣服 | 5000 |
| 办公用品 | 600 |
| 厨房用具 | 11180 |
如果想要获得那样的结果，通常的做法是分别计算出合计行和按照商品种类进行汇总的结果，然后通过 UNION ALL 连接在一起（代码清单8-11）。
SELECT '合计' AS product_type, SUM(sale_price)
FROM Product
UNION ALL
SELECT product_type, SUM(sale_price)
FROM Product
GROUP BY product_type;
| product_type | sum |
|---|---|
| 合计 | 16780 |
| 衣服 | 5000 |
| 办公用品 | 600 |
| 厨房用具 | 11180 |
这样一来，为了得到想要的结果，需要执行两次几乎相同的SELECT语句，再将其结果进行连接，不但看上去十分繁琐，而且DBMS内部的处理成本也非常高，难道没有更合适的实现方法了吗？
ROLLUP——同时得出合计和小计
ROLLUP的中文翻译是
汇总，卷曲，比如卷起百叶窗、窗帘卷，等等。其名称也形象地说明了该操作能够得到像从小计到合计这样，从最小的聚合级开始，聚合单位逐渐扩大的结果。
GROUPING运算符包含以下3种
ROLLUPCUBEGROUPING SETS
该运算符的作用，一言以蔽之，就是“一次计算出不同聚合键组合的结果”
SELECT product_type, SUM(sale_price) AS sum_price
FROM Product
GROUP BY ROLLUP(product_type);
■将“登记日期”添加到聚合键当中
在GROUP BY中添加“登记日期”（不使用ROLLUP）
SELECT product_type, regist_date, SUM(sale_price) AS sum_price
FROM Product
GROUP BY product_type, regist_date;
在GROUP BY中添加“登记日期”（使用ROLLUP）
SELECT product_type, regist_date, SUM(sale_price) AS sum_price
FROM Product
GROUP BY ROLLUP(product_type, regist_date);
GROUPING函数——让NULL更加容易分辨
之前使用ROLLUP所得到的结果有些蹊跷，问题就出在“衣服”的分组之中，有两条记录的regist_date列为NULL，但其原因却并不相同。
为了避免混淆，SQL提供了一个用来判断超级分组记录（合计行记录）的NULL的特定函数—— GROUPING函数。
该函数在其参数列的值为超级分组记录所产生的NULL时返回1，其他情况返回0。
SELECT GROUPING(product_type) AS product_type,
GROUPING(regist_date) AS regist_date, SUM(sale_price) AS sum_price
FROM Product
GROUP BY ROLLUP(product_type, regist_date);
这样就能分辨超级分组记录中的NULL和原始数据本身的NULL了。
在超级分组记录的键值中插入恰当的字符串
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 NVARCHAR(16))
END AS regist_date, SUM(sale_price) AS sum_price
FROM Product
GROUP BY ROLLUP(product_type, regist_date);
CUBE——用数据来搭积木
CUBE是“立方体”的意思，这个名字和ROLLUP一样，都能形象地说明函数的动作。
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 NVARCHAR(16))
END AS regist_date, SUM(sale_price) AS sum_price
FROM Product
GROUP BY cube(product_type, regist_date)
在ROLLUP的基础上多出几条，多出来的记录就是只把regist_date作为聚合键所得到的汇总结果。
CUBE与ROLLUP的区别
等价于
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 NVARCHAR(16))
END AS regist_date, SUM(sale_price) AS sum_price
FROM Product
GROUP BY ROLLUP(product_type, regist_date)
UNION
SELECT '商品种类 合计',CAST(regist_date AS VARCHAR(16)),SUM(sale_price) FROM Product
GROUP BY regist_date
所谓CUBE，就是将GROUP BY子句中聚合键的“所有可能的组合”的汇总结果集中到一个结果中。因此，组合的个数就是2n（n是聚合键的个数）。本例中聚合键有2个，所以22 = 4。如果再添加1个变为3个聚合键的话，就是23 = 8
GROUPING SETS——取得期望的积木
如果希望从中选取出将“商品种类”和“登记日期”各自作为聚合键的结果，或者不想得到“合计记录和使用2个聚合键的记录”时，可以使用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);
上述结果中也没有全体的合计行（16780日 元 ）。 与ROLLUP或者CUBE能够得到规定的结果相对，GROUPING SETS用于从中取出个别条件对应的不固定的结果。
最新文章
- Datatable删除行的Delete和Remove方法
- 自定义getElementByClass
- 设计模式学习之模板方法模式（TemplateMethod,行为型模式）（9）
- 高级Javascript调试——console.table()
- jQuery--index() window.onhashchange
- CPU相关信息
- Makefile第三讲：终端传值给Makefile、Makefile传值给C++代码
- haproxy nginx 多路径
- widget intent重复问题
- BZOJ 1592: [Usaco2008 Feb]Making the Grade 路面修整( dp )
- Hibernate 系列教程17-查询缓存
- CentOS7.2编译安装PHP7.2.3之史上最详细步骤。
- java~集合的介绍和使用
- table-layout:fixed; 表格比例固定
- 32)django-modelform
- 用python读写excel的强大工具：openpyxl
- IDEA 的 Edit 设置
- HDU 4123 Bob’s Race 树形dp+单调队列
- Linux获取当前时间
- 设备树驱动API【原创】