SQL Server 2008 新特性 總結(jié)復(fù)習(xí)（一）_Mssql數(shù)據(jù)庫教程

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

--公用表達(dá)式CTE Common table expression

--用CTE實現(xiàn)遞歸算法

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)


--不報錯設(shè)置級聯(lián)關(guān)聯(lián)遞歸
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)

pivot與unpivot

--關(guān)于PIVOT的操作

CREATE TABLE #test
(
NAME VARCHAR(max),
SCORE INT
)

INSERT INTO #test VALUES ('張三','97')
INSERT INTO #test VALUES ('李四','28')
INSERT INTO #test VALUES ('王五','33')
INSERT INTO #test VALUES ('神人','78')

--NAME SCORE
--張三 97
--李四 28
--王五 33
--神人 78

--行轉(zhuǎn)列
SELECT --'成績單' AS SCORENAME ,
[張三], [李四], [王五]
FROM #test
PIVOT (AVG(SCORE) FOR NAME IN ([張三], [李四], [王五])) b


-----------------------------------------

CREATE TABLE VendorEmployee(
VendorId INT,
Emp1Order INT,
Emp2Order INT,
Emp3Order INT,
Emp4Order INT,
Emp5Order INT,
)

GO

INSERT INTO VendorEmployee VALUES(1,4,3,5,4,4)
INSERT INTO VendorEmployee VALUES(2,4,1,5,5,5)
INSERT INTO VendorEmployee VALUES(3,4,3,5,4,4)
INSERT INTO VendorEmployee VALUES(4,4,2,5,4,4)
INSERT INTO VendorEmployee VALUES(5,5,1,5,5,5)

SELECT * FROM VendorEmployee

----------------
--列轉(zhuǎn)行

SELECT * FROM (
SELECT VendorId,[Emp1Order],[Emp2Order],[Emp3Order],[Emp4Order],[Emp5Order] FROM VendorEmployee) AS unpiv
UNPIVOT (orders FOR elyid IN ([Emp1Order],[Emp2Order],[Emp3Order],[Emp4Order],[Emp5Order])) AS child
ORDER BY elyid

SELECT * FROM VendorEmployee
UNPIVOT (orders FOR elyid IN ([Emp1Order],[Emp2Order],[Emp3Order],[Emp4Order],[Emp5Order])) AS child
ORDER BY elyid

SELECT * FROM VendorEmployee UNPIVOT ( ORDERS FOR [操作員名字] IN ([Emp1Order],[Emp2Order],[Emp3Order],[Emp4Order],[Emp5Order]))