《SQL基础教程》

于 2018-04-28 14:34:00 发布
阅读量1.5k 收藏 10
点赞数 2
文章标签： 数据库

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

196558-20180502094913441-1639685970.png

第四章 数据的更新

　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

196558-20180502134620059-1582540627.png

第五章 复杂查询

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;

返回结果是多行结果，所以不能使用标量子查询。
196558-20180502172838690-1397508405.png

这个时候改使用关联子查询。

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);

196558-20180502172823841-37740366.png
该条件的意思就是，在同一商品种类中对各商品的销售单价和平均单价进行比较。
在对表中某一部分记录的集合进行比较时，就可以使用关联子查询。

在细分的组内进行比较时，需要使用关联子查询。

大家还记得我们用来说明GROUP BY子句的图
196558-20180502173255806-1118702481.png
使用关联子查询进行切分的图示也基本相同
196558-20180502173429001-2102411933.png

我们首先需要计算各个商品种类中商品的平均销售单价，由于该单价会用来和商品表中的各条记录进行比较，因此关联子查询实际只能返回1行结果。这也是关联子查询不出错的关键。

关联子查询执行时DBMS内部的执行情况
196558-20180502173451207-2113004503.png

练习题：

5.1 创建出满足下述三个条件的视图（视图名称为ViewPractice5_1）。 使用Product（商品）表作为参照表，假设表中包含初始状态的 8行数据。
条件 1： 销售单价大于等于1000日元。
条件 2： 登记日期是2009年 9月 20日。
条件 3： 包含商品名称、销售单价和登记日期三列。
对该视图执行SELECT语句的结果如下所示。

SELECT * FROM ViewPractice5_1;

执行结果

product_namesale_priceregist_date
T恤衫10002009-09-20
菜刀30002009-09-20

5.2 向习题 5.1 中创建的视图 ViewPractice5_1中插入如下数据，会得到什么样的结果呢？

INSERT INTO ViewPractice5_1 VALUES ('刀子', 300, '2009-11-02');

　
5.3 请根据如下结果编写 SELECT语句，其中 sale_price_all列为全部商品的平均销售单价。

product_idproduct_nameproduct_typesale_pricesale_price_all
0001T恤衫衣服10002097.5000000000000000
0002打孔器办公用品5002097.5000000000000000
0003运动T恤衣服40002097.5000000000000000
0004菜刀厨房用具30002097.5000000000000000
0005高压锅厨房用具68002097.5000000000000000
0006叉子厨房用具5002097.5000000000000000
0007擦菜板厨房用具8802097.5000000000000000
0008圆珠笔办公用品1002097.5000000000000000

5.4 请根据习题5.1中的条件编写一条SQL语句，创建一幅包含如下数据的视图（名称为 AvgPriceByType）。
执行结果

product_idproduct_nameproduct_typesale_priceavg_sale_price
0001T恤衫衣服10002500.0000000000000000
0002打孔器办公用品500300.0000000000000000
0003运动T恤衣服40002500.0000000000000000
0004菜刀厨房用具30002795.0000000000000000
0005高压锅厨房用具68002795.0000000000000000
0006叉子厨房用具5002795.0000000000000000
0007擦菜板厨房用具8802795.0000000000000000
0008圆珠笔办公用品100300.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;

196558-20180503165937681-116234923.png

  • SUBSTRING——字符串的截取
--（PostgreSQL/MySQL专用语法）
SUBSTRING（对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
-- sqlserver专用
SUBSTRING(对象字符串，截取的起始位置，截取的字符数)

SELECT str1,
       SUBSTRING(str1, 3, 2) AS sub_str
  FROM SampleStr;

196558-20180503170230136-1758824027.png

  • 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;

196558-20180503173352086-946258079.png

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_namesale_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;

196558-20180504101823710-594959213.png

  • CASE表达式的书写位置

    CASE表达式的便利之处就在于它是一个表达式。之所以这么说，是因为表达式可以书写在任意位置，也就是像“1 + 1”这样写在什么位置都可以的意思。例如，我们可以利用CASE表达式将下述SELECT语句结果中的行和列进行互换。
    要实现下面的结果：
sum_price_clothessum_price_kitchensum_price_office
500011180600

上述结果是根据商品种类计算出的销售单价的合计值，通常我们将商品种类列作为GROUP BY子句的聚合键来使用，但是这样得到的结果会以“行”的形式输出，而无法以列的形式进行排列。

SELECT product_type,
       SUM(sale_price) AS sum_price
  FROM Product
 GROUP BY product_type;

但是结果是：

product_typesum_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_pricemid_pricehigh_price
512

答案：

 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_nameproduct_typesale_priceranking
叉子厨房用具5001
擦菜板厨房用具8802
菜刀厨房用具30003
高压锅厨房用具68004
T恤衫衣服10001
运动T恤衣服40002
圆珠笔办公用品1001
打孔器办公用品5002
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来指定升序和降序。
196558-20180504114054013-999158488.png

窗口函数兼具分组和排序两种功能。

通过 PARTITION BY分组后的记录集合称为窗口。此处的窗口并非“窗户”的意思，而是代表范围。这也是“窗口函数”名称的由来。

无需指定PARTITION BY

SELECT product_name, product_type, sale_price, 
       RANK () OVER (ORDER BY sale_price) AS ranking
  FROM Product;
product_nameproduct_typesale_priceranking
圆珠笔办公用品1001
叉子厨房用具5002
打孔器办公用品5002
擦菜板厨房用具8804
T恤衫衣服10005
菜刀厨房用具30006
运动T恤衣服40007
高压锅厨房用具68008

专用窗口函数的种类

  • 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;

196558-20180504115259335-1800876045.png

作为窗口函数使用的聚合函数

  • 将SUM函数作为窗口函数使用
SELECT product_id, product_name, sale_price,
    　SUM(sale_price) OVER (ORDER BY product_id) AS current_sum
  FROM Product;

196558-20180504115851249-565605702.png
计算该合计值的逻辑就像金字塔堆积那样，一行一行逐渐添加计算对象。在按照时间序列的顺序，计算各个时间的销售额总额等的时候，通常都会使用这种称为累计的统计方法。

  • 将AVG函数作为窗口函数使用
SELECT product_id, product_name, sale_price,
     AVG (sale_price) OVER (ORDER BY product_id) AS current_avg
  FROM Product;

196558-20180504120255874-529152471.png

计算移动平均

窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。其实其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架。
需要在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;

196558-20180504121331226-1636318702.png

这里我们使用了ROWS（“行”）和PRECEDING（“之前”）两个关键字，将框架指定为“截止到之前~行 ”， 因 此 “ROWS 2 PRECEDING”就是将框架指定为“截止到之前2行”，也就是将作为汇总对象的记录限定为如下的“最靠近的3行 ”
196558-20180504121546027-2126103347.png
这样的统计方法称为移动平均（moving average）。由于这种方法在希望实时把握“最近状态”时非常方便，因此常常会应用在对股市趋势的实时跟踪当中。
使用关键字FOLLOWING（“ 之 后 ”） 替 换PRECEDING，就可以指定“截止到之后~行”作为框架了（图8-3）。
196558-20180504121707958-721977079.png

8-2 GROUPING运算符

同时得到合计行

196558-20180504140832308-1133328175.png
使用GROUP BY无法得到合计行

SELECT product_type, SUM(sale_price)
  FROM Product
 GROUP BY product_type;
product_typesum
衣服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_typesum
合计16780
衣服5000
办公用品600
厨房用具11180

这样一来，为了得到想要的结果，需要执行两次几乎相同的SELECT语句，再将其结果进行连接，不但看上去十分繁琐，而且DBMS内部的处理成本也非常高，难道没有更合适的实现方法了吗？

ROLLUP——同时得出合计和小计

ROLLUP的中文翻译是汇总，卷曲，比如卷起百叶窗、窗帘卷，等等。其名称也形象地说明了该操作能够得到像从小计到合计这样，从最小的聚合级开始，聚合单位逐渐扩大的结果。

GROUPING运算符包含以下3种

  • ROLLUP
  • CUBE
  • GROUPING SETS

该运算符的作用，一言以蔽之，就是“一次计算出不同聚合键组合的结果”

SELECT product_type, SUM(sale_price) AS sum_price
  FROM Product
 GROUP BY ROLLUP(product_type);

196558-20180504141613539-74778981.png
■将“登记日期”添加到聚合键当中
在GROUP BY中添加“登记日期”（不使用ROLLUP）

SELECT product_type, regist_date, SUM(sale_price) AS sum_price
  FROM Product
 GROUP BY product_type, regist_date;

196558-20180504142936654-2065285082.png
在GROUP BY中添加“登记日期”（使用ROLLUP）

 SELECT product_type, regist_date, SUM(sale_price) AS sum_price
  FROM Product
 GROUP BY ROLLUP(product_type, regist_date);

196558-20180504143320893-675631983.png

GROUPING函数——让NULL更加容易分辨

之前使用ROLLUP所得到的结果有些蹊跷，问题就出在“衣服”的分组之中，有两条记录的regist_date列为NULL，但其原因却并不相同。
196558-20180504173715964-627913883.png

为了避免混淆，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);

196558-20180504174451041-250777603.png
这样就能分辨超级分组记录中的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);

196558-20180504175538016-1310784976.png

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)

196558-20180507100102269-680492051.png
在ROLLUP的基础上多出几条，多出来的记录就是只把regist_date作为聚合键所得到的汇总结果。

CUBE与ROLLUP的区别
196558-20180507101022039-1425977408.png

等价于

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
196558-20180507103639780-969497777.png

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);

196558-20180507105205638-1245099352.png
上述结果中也没有全体的合计行（16780日 元 ）。 与ROLLUP或者CUBE能够得到规定的结果相对，GROUPING SETS用于从中取出个别条件对应的不固定的结果。

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值