您的位置: 旅游网 > 影视

谈SQL Server 2005中的T-SQL增强(7)

发布时间:2019-09-13 20:37:51

新的关系运算符 PIVOT/UNPIVOT/APPLY 1、PIVOT PIVOT运算符将行旋转为列,并且可能同时执行聚合。使用PIVOT运算符时要注意的重要一点是,需要为它提供一个查询表达式,表达式使用视图、派生表或者是CTE只返回所关注的列。 2、UNPIVOT UNPIVOT运算符执行与PIVOT运算符相反的操作;他将列旋转为行了。 3、APPLY APPLY关系运算符允许您对外部表的每个行调用指定的表值函数一次。您可以在查询的FROM子句中指定APPLY,其方式与使用JOIN关系运算符类似。APPLY具有两种形式:CROSS APPLY和OUTER APPLY。 演示: USE demoGOCREATE TABLE orders(Customer VARCHAR(10) NOT NULL,product VARCHAR(20) NOT NULL,quantity INT NOT NULL)GOINSERT orders VALUES('Mike', 'Bike',3)INSERT orders VALUES('Mike','Chain',2)INSERT orders VALUES('Mike','Bike',5)INSERT orders VALUES('Lisa','Bike',3)INSERT orders VALUES('Lisa','Chain',3)INSERT orders VALUES('Lisa','Chain',4)INSERT orders VALUES('Lisa','Bike',2)SELECT * FROM ordersSELECT * FROM ordersPIVOT (SUM(quantity) FOR product IN ([Bike],[Chain])) AS aUSE demoGOCREATE TABLE SALES1([Year] INT,Quarter CHAR(2),Amount FLOAT)GOINSERT INTO SALES1 VALUES (2001, 'Q1', 80)INSERT INTO SALES1 VALUES (2001, 'Q2', 70)INSERT INTO SALES1 VALUES (2001, 'Q3', 55)INSERT INTO SALES1 VALUES (2001, 'Q3', 110)INSERT INTO SALES1 VALUES (2001, 'Q4', 90)INSERT INTO SALES1 VALUES (2002, 'Q1', 200)INSERT INTO SALES1 VALUES (2002, 'Q2', 150)INSERT INTO SALES1 VALUES (2002, 'Q2', 40)INSERT INTO SALES1 VALUES (2002, 'Q2', 60)INSERT INTO SALES1 VALUES (2002, 'Q3', 120)INSERT INTO SALES1 VALUES (2002, 'Q3', 110)INSERT INTO SALES1 VALUES (2002, 'Q4', 180)GOSELECT * FROM SALES1PIVOT(SUM (Amount) --使用SUM聚合数量列FOR [Quarter] --PIVOT Quarter 列IN (Q1, Q2, Q3, Q4)) --使用季节AS PGOSELECT * INTO temp1 FROM ordersPIVOT (sum(quantity) FOR product IN ([Bike],[Chain])) AS aSELECT * FROM temp1SELECT customer, product,quantityFROM temp1UNPIVOT(quantity FOR product IN ([Bike],[Chain])) AS a----------------------------------------------------USE demoGOCREATE TABLE Arrays(aid INT NOT NULL IDENTITY PRIMARY KEY,array VARCHAR(7999) NOT NULL)GOINSERT INTO Arrays VALUES('')INSERT INTO Arrays VALUES('10')INSERT INTO Arrays VALUES('20,40,30')INSERT INTO Arrays VALUES('-1,-3,-5')GOCREATE FUNCTION function1(@arr AS VARCHAR(7999))RETURNS @t TABLE(pos INT NOT NULL, value INT NOT NULL)ASBEGINDECLARE @end AS INT, @start AS INT, @pos AS INTSELECT @arr = @arr + ',', @pos = 1,@start = 1, @end = CHARINDEX(',', @arr, @start)WHILE @end > 1BEGININSERT INTO @t VALUES(@pos, SUBSTRING(@arr, @start, @end - @start))SELECT @pos = @pos + 1,@start = @end + 1, @end = CHARINDEX(',', @arr, @start)ENDRETURNEND--测试SELECT * FROM function1('200,400,300')GOSELECT A.aid, F.*FROM Arrays AS ACROSS APPLY function1(array) AS FGOSELECT A.aid, F.*FROM Arrays AS AOUTER APPLY function1(array) AS FGO

新生儿黄疸的症状表现
小儿风寒咳嗽
如何消除血栓
小孩营养不良的症状
猜你会喜欢的
猜你会喜欢的