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);
--
1 | 2 | 3 | 4 |
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), () );
OnionYang@