--SQL Server 2008程序设计之 cte递归查询中限制递归层次，PIVOT和UNPIVOT表旋转的应用 /******************************************************************************** *主题：SQL Server 2008程序设计之 *说明：本文是个人学习的一些笔记和个人愚见 * 有很多地方你可能觉得有异议，欢迎一起讨论 *作者：Stephenzhou(阿蒙) *日期: 2012.12.3 *Mail:[email protected] *另外：转载请著名出处。 **********************************************************************************/
--精通Sql2008 程序设计
--cte查询
--如下表
--如下表/*SELECT * FROM employeetreeemployeeid employeename reportsto----------- -------------------------------------------------- -----------1 Richard NULL2 Stephen 13 Clemens 24 Malek 25 Goksin 46 Kimberly 17 Ramesh 5(7 行受影响)*/
--找出Stephen下面的员工和对应的领导
;with ctas(select * from employeetree where employeeid=2union allselect e.* from employeetree e inner join ct c on e.reportsto=c.employeeid)select c.employeename,e.employeename from ct c join employeetree e on c.reportsto=e.employeeid/*employeename employeename-------------------------------------------------- --------------------------------------------------Stephen RichardClemens StephenMalek StephenGoksin MalekRamesh Goksin(5 行受影响)*/
--option (maxrecursion 25)
;with ctas(select * from employeetree where employeeid=2union allselect e.* from employeetree e inner join ct c on e.reportsto=c.employeeid)select c.employeename,e.employeename from ct c join employeetree e on c.reportsto=e.employeeidoption(maxrecursion 2)/*employeename employeename-------------------------------------------------- --------------------------------------------------Stephen RichardClemens StephenMalek StephenGoksin Malek消息 530，级别 16，状态 1，第 1 行语句被终止。完成执行语句前已用完最大递归 2。*/
--说明：在开发中，有递归的限制，默认的限制为100 如果超过100 必须设置 option(Maxrecursion 0) 没有最大的限制
--但是一般在开发中不需要限制这个递归的层次，只需要在后面跟个where来限制需要的层次就好了
--PIVOT 和UNPIVOT运算符
create table VendorEmployee(VendorID int,Emp1orders int,Emp2Orders int,Emp3Orders int,Emp4Orders int,Emp5Orders int)goinsert 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 /* VendorID Emp1orders Emp2Orders Emp3Orders Emp4Orders Emp5Orders----------- ----------- ----------- ----------- ----------- -----------1 4 3 5 4 42 4 1 5 5 53 4 3 5 4 44 4 2 5 4 45 5 1 5 5 5(5 行受影响) */
UNPIVOT转换
select * from (select * from VendorEmployee)punpivot(Orfder for Employee in (Emp1orders,Emp2Orders,Emp3Orders,Emp4Orders,Emp5Orders))s /*VendorID Orfder Employee----------- ----------- --------------------------------------------------------1 4 Emp1orders1 3 Emp2Orders1 5 Emp3Orders1 4 Emp4Orders1 4 Emp5Orders2 4 Emp1orders2 1 Emp2Orders2 5 Emp3Orders2 5 Emp4Orders2 5 Emp5Orders3 4 Emp1orders3 3 Emp2Orders3 5 Emp3Orders3 4 Emp4Orders3 4 Emp5Orders4 4 Emp1orders4 2 Emp2Orders4 5 Emp3Orders4 4 Emp4Orders4 4 Emp5Orders5 5 Emp1orders5 1 Emp2Orders5 5 Emp3Orders5 5 Emp4Orders5 5 Emp5Orders(25 行受影响)*/
--把上面的表再复制到别的表上去然后再pivot下旋转下看看
--如下
select * into ##aa from ( select * from (select * from VendorEmployee)punpivot(Orfder for Employee in (Emp1orders,Emp2Orders,Emp3Orders,Emp4Orders,Emp5Orders))s )f
--执行旋转。povit
select * from (select * from ##aa)p pivot (sum(Orfder) for Employee in (Emp1orders,Emp2Orders,Emp3Orders,Emp4Orders,Emp5Orders))f order by vendorID/*VendorID Emp1orders Emp2Orders Emp3Orders Emp4Orders Emp5Orders----------- ----------- ----------- ----------- ----------- -----------1 4 3 5 4 42 4 1 5 5 53 4 3 5 4 44 4 2 5 4 45 5 1 5 5 5(5 行受影响)*/-- 很简单的两个旋转来旋转去的。
*作者：Stephenzhou(阿蒙)
*日期: 2012.12.3
*Mail:[email protected]
*另外：转载请著名出处。
*博客地址：http://blog.csdn.net/szstephenzhou