SQL Server 2008 新特性 总结复习（一）

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

--公用表达式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)

pivot与unpivot

--关于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

--行转列
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

----------------
--列转行

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