Grouping by分组集温习_2

疫情时期看过，最近再温习一下

--group by 子句中的cube,  rollup，
--这样理解：  cube立方(既然是立方，就是变多了)，  rollup卷起，包起来(按字面意思理解)

IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
GO

CREATE TABLE dbo.Orders
(
  orderid   INT        NOT NULL,
  orderdate DATE       NOT NULL, -- prior to SQL Server 2008 use DATETIME
  empid     INT        NOT NULL,
  custid    VARCHAR(5) NOT NULL,
  qty       INT        NOT NULL,
  CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);

INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES
  (30001, '20070802', 3, 'A', 10),
  (10001, '20071224', 2, 'A', 12),
  (10005, '20071224', 1, 'B', 20),
  (40001, '20080109', 2, 'A', 40),
  (10006, '20080118', 1, 'C', 14),
  (20001, '20080212', 2, 'B', 12),
  (40005, '20090212', 3, 'A', 10),
  (20002, '20090216', 1, 'C', 20),
  (30003, '20090418', 2, 'B', 15),
  (30004, '20070418', 3, 'C', 22),
  (30007, '20090907', 3, 'D', 30);

SELECT empid,custid,sum(qty) as sumqty
FROM Orders
GROUP BY empid,custid;

SELECT empid,sum(qty) as sumqty
FROM Orders
GROUP BY empid

SELECT custid,sum(qty) as sumqty
FROM Orders
GROUP BY custid

SELECT sum(qty) as sumqty
FROM Orders

1）假设现在不想生成4个单独的结果集，而是希望生成一个统一的结果集，其中包含所有4个分组集的聚合数据，

为了实现该目标，可使用Union all 结合运算，将4个结果集合并在一起
2）使用GROUPING BY的子句 Grouping sets将几个分组的子句都包含进来， Group by 子句可以是 Grouping sets , Cube, Rollup 从属子句

SELECT empid,custid,
       sum(qty) as sumqty
FROM Orders
GROUP BY
    GROUPING SETS
    (
        (empid,custid),
        (empid),(custid),
        ()
    );