mssql server 2008r2 一列如何拆分成多行
先介绍两个函数:cross apply和outer apply。这两个函数作用是交叉连接。这两个函数是在sql server 2005之后才有
在2000与之相似的功能是cross join。虽然相似,但是cross join有一个致命功能缺陷。详看代码:
SELECT * FROM TEST01 AS T01 CROSS JOIN FUNC_TB2(T01.FIELD1)
--FUNC_TB2为表值函数
执行此sql后,将报错。详细错误信息,如下:Msg 4104, Level 16, State 1, Line 1.The multi-part identifier "T01.FIELD1" could not be bound。由此可见,cross join不能接受由TEST01传过去的值。由于cross join这样的缺陷,所以sql server 在2005版本后新增了cross apply和outer apply,二者可以完全弥补这一缺陷。cross apply虽然与outer apply功能相似,但是二者也有不同。cross apply与FUNC_TB2交集的结果将去除右边NULL项,而outer apply将包括NULL项。
下面有这样一张表TakeNo,数据是:
id | WindowNos |
---|---|
1 | A53,A48,A49 |
2 | A45,A46 |
3 | M24 |
期望转换成
id | WindowNos |
---|---|
1 | A53 |
1 | A48 |
1 | A49 |
2 | A45 |
2 | A46 |
3 | M24 |
分两步: |
- 将逗号隔开的数据分割字符串xml格式:
<root><v>A53</v><v>A48</v><v>A49</v></root>
<root><v>A45</v><v>A46</v></root>
<root><v>M24</v></root>
- 和id进行关联即可。
代码如下:
SELECT B.WindowNo
FROM(
SELECT WindowNo=CONVERT(xml,'<root><v>' + REPLACE(WindowNos, ',', '</v><v>') + '</v></root>')
FROM TakeNo
WHERE Status='0'
)AS A
OUTER APPLY(
SELECT WindowNo=N.a.value('.', 'varchar(100)') FROM A.WindowNo.nodes('/root/v') N(a)
)AS B
最后分下组,取前20个并倒序看下
SELECT TOP 20 COUNT(1) AS Count,WindowNo FROM (
SELECT B.WindowNo
FROM(
SELECT WindowNo=CONVERT(xml,'<root><v>' + REPLACE(WindowNos, ',', '</v><v>') + '</v></root>')
FROM TakeNo
WHERE Status='0'
)AS A
OUTER APPLY(
SELECT WindowNo=N.a.value('.', 'varchar(100)') FROM A.WindowNo.nodes('/root/v') N(a)
)AS B
) AS T GROUP BY T.WindowNo ORDER BY Count DESC