SQL Server 总结复习(一)

复制代码 代码如下:

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)

复制代码 代码如下: