--数据准备
--表值函数 dbo.SplitString:按照分隔符将文本转换为表 declare @str1 varchar(max) set @str1 = ‘7,8,9‘ SELECT * FROM dbo.SplitString(@str1,‘,‘,1) --示例:某表有Col1,Col2两列,想将Col2中的文本拆分为多行,并在拆分后保留Col1和Col2的对应关系 CREATE TABLE #TMP( NAME VARCHAR(100) NULL, STRING VARCHAR(MAX) NULL ) TRUNCATE TABLE #TMP INSERT INTO #TMP(NAME, STRING) VALUES(‘TEST111‘,‘2,3,4‘), (‘TEST222‘,‘6,7‘), (‘TEST222‘,null)
使用情形:
1. 想将某表关联表值函数返回的表
例如:某表有Col1,Col2两列,想将Col2中的文本拆分为多行,并在拆分后保留Col1和Col2的对应关系
--方法1:使用游标,逐行拆分后拼接(略)
--方法2:使用APPLY进行逐行运算
--根据某列一行变多行,并保留其他列的对应关系 SELECT C.NAME, A.Value FROM #TMP C CROSS APPLY dbo.SplitString(C.STRING,‘,‘,1) A --去除NULL列 SELECT C.NAME, A.Value FROM #TMP C OUTER APPLY dbo.SplitString(C.STRING,‘,‘,1) A --保留NULL列
2. 两表关联每个客户的前三笔交易,并展示客户信息
--方法1:用JOIN + ROW_NUMBER()
--方法2:用APPLY + TOP
--每个客户的前三笔交易 --方法1:用ROW_NUMBER() SELECT C.custid, C.companyname, A.orderid, A.orderdate FROM Sales.Customers C LEFT JOIN( SELECT ROW_NUMBER()OVER(PARTITION BY custid ORDER BY orderdate DESC, orderid DESC) AS RN , custid, orderid, empid, orderdate, requireddate FROM Sales.Orders ) A ON C.custid=A.custid WHERE A.RN <=3 ORDER BY custid, RN --方法2:用APPLY + TOP SELECT C.custid, C.companyname, A.orderid, A.orderdate FROM Sales.Customers C CROSS APPLY( SELECT TOP 3 orderid, empid, orderdate, requireddate FROM Sales.Orders O WHERE O.custid=C.custid ORDER BY orderdate DESC, orderid DESC) A ORDER BY C.custid