復制代碼 代碼如下:

With cube, With rollup
--示例代碼
declare @t table(goodsname VARCHAR(max) ,sku1name VARCHAR(max) , sku2name VARCHAR(max), qty INT)
insert @t select '凡客TX','紅色','S',1
insert @t select '凡客TX','黑色','S',2
insert @t select '凡客TX','白色','L',3
insert @t select '京東村山','白色','L',4
insert @t select '京東村山','紅色','S',5
insert @t select '京東村山','黑色','L',6
insert @t select '亞馬遜拖鞋','白色','L',7
insert @t select '亞馬遜拖鞋','紅色','S',8
SELECT * FROM @t
select goodsname,sku1name,sku2name,sum(qty) sumqty
from @t
group by goodsname,sku1name,sku2name with rollup
ORDER BY goodsname,sku1name,sku2name
select goodsname,sku1name,sku2name,sum(qty) sumqty
from @t
group by goodsname,sku1name,sku2name with cube
ORDER BY goodsname,sku1name,sku2name
-----------------------
declare @t table(goodsname VARCHAR(max) ,sku1name VARCHAR(max) , sku2name VARCHAR(max), qty INT)
insert @t select '凡客TX','紅色','S',1
insert @t select '凡客TX','黑色','S',2
insert @t select '凡客TX','白色','L',3
insert @t select '京東村山','白色','L',4
insert @t select '京東村山','紅色','S',5
insert @t select '京東村山','黑色','L',6
insert @t select '亞馬遜拖鞋','白色','L',7
insert @t select '亞馬遜拖鞋','紅色','S',8
--GROUPING SETS 運算符
SELECT goodsname,sku1name,sku2name, SUM(qty) FROM @t GROUP BY GROUPING SETS(goodsname,sku1name,sku2name)
SELECT goodsname, sku1name, sku2name ,SUM(qty) FROM @t
GROUP BY GROUPING SETS(goodsname), ROLLUP(sku1name,sku2name)
ORDER BY goodsname,sku1name,sku2name
SELECT goodsname, sku1name, sku2name ,SUM(qty) FROM @t
GROUP BY ROLLUP(goodsname,sku1name,sku2name)
ORDER BY goodsname,sku1name,sku2name
SELECT CASE WHEN GROUPING(goodsname) = 1 THEN '[ALL]' ELSE goodsname END goodsname,
CASE WHEN GROUPING(sku1name) = 1 THEN '[ALL]' ELSE sku1name END sku1name,
CASE WHEN GROUPING(sku2name) = 1 THEN '[ALL]' ELSE sku2name END sku2name ,SUM(qty) FROM @t
GROUP BY GROUPING SETS(goodsname), ROLLUP(sku1name,sku2name)
ORDER BY goodsname,sku1name,sku2name

復制代碼 代碼如下:

--公用表達式CTE Common table expression
--用CTE實現遞歸算法
CREATE TABLE EMPLOYEETREE(
EMPLOYEE INT PRIMARY KEY,
employeename nvarchar(50),
reportsto int
)
insert into EMPLOYEETREE values(1,'Richard',null)
insert into EMPLOYEETREE values(2,'Stephen',1)
insert into EMPLOYEETREE values(3,'Clemens',2)
insert into EMPLOYEETREE values(4,'Malek',2)
insert into EMPLOYEETREE values(5,'Goksin',4)
insert into EMPLOYEETREE values(6,'Kimberly',1)
insert into EMPLOYEETREE values(7,'Ramesh',5)
----------------------
--確定哪些員工向Stephen報告的遞歸查詢
with employeeTemp as
(
select EMPLOYEE, employeename, reportsto from EMPLOYEETREE where EMPLOYEE = 2
union all
select a.EMPLOYEE, a.employeename, a.reportsto from EMPLOYEETREE as a
inner join employeeTemp as b on a.reportsto = b.EMPLOYEE
)
select * from employeeTemp where EMPLOYEE <> 2 --option(maxrecursion 2)
--不報錯設置級聯關聯遞歸
with employeeTemp as
(
select EMPLOYEE, employeename, reportsto,0 as sublevel from EMPLOYEETREE where EMPLOYEE = 2
union all
select a.EMPLOYEE, a.employeename, a.reportsto,sublevel+1 from EMPLOYEETREE as a
inner join employeeTemp as b on a.reportsto = b.EMPLOYEE
)
select * from employeeTemp where EMPLOYEE <> 2 and sublevel <=2 --option(maxrecursion 2)

復制代碼 代碼如下: