T-SQL Recipes之生成动态列表数据
Problem
首先什么是动态列表?举个示例,假设你想输出以逗号分隔的IDs,如:
1,45,67,199,298
Solution
生成动态列表数据在我们生活场景中很常见,比如在 Dynamic PIVOT中,解决方案也有许多种,小陈知道的大体有:
Cursor-Based
XML-Based
Set-Based
这三种方式,性能从低到高,有兴趣的看官可以自己看一下执行计划,分析数据。这里主要演示代码和思路为主。
Cursor-Based Approach
USE AdventureWorks2014; GO DECLARE @nextid INT; DECLARE @myIDs NVARCHAR(MAX) = ''; DECLARE idcursor CURSOR LOCAL FAST_FORWARD FOR SELECT TOP 10 SalesOrderID FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC; OPEN idcursor; FETCH NEXT FROM idcursor INTO @nextid; WHILE @@FETCH_STATUS = 0 BEGIN SET @myIDs = @myIDs + CAST(@nextid AS NVARCHAR) + N','; FETCH NEXT FROM idcursor INTO @nextid; END --SET @myIDs = SUBSTRING(@myIDs, 1, LEN(@myIDs) -1) SET @myIDs = STUFF(@myIDs,LEN(@myIDs), 1, '') SELECT @myIDs AS comma_separated_output;
分析:Cursor-Based 的思路是迭代想要的结果集,一次一次加上,最后去掉最后一个逗号。关于去掉最后一个逗号,或者刚开始的,小陈推荐使用 STUFF() 函数.
PS: 小陈觉得应该在T-SQL私房菜中加一道常见有用函数的菜谱。
XML-Based Approach
USE AdventureWorks2014; GO DECLARE @myIDs NVARCHAR(MAX) = ''; SET @myIDs = STUFF( (SELECT TOP 10 N',' + CAST(SalesOrderID AS NVARCHAR) AS [text()] FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC FOR XML PATH('')), 1, 1, ''); /* SET @myIDs = STUFF( (SELECT TOP 10 ',' + CAST(SalesOrderID AS NVARCHAR) FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC FOR XML PATH(''),TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); */ SELECT @myIDs AS comma_separated_output;
分析:XML-Based 方式,初看之下觉得很复杂,不管怎么样,可读性上真的很差。对于看似复杂的T-SQL,小陈建议一步一步的分解,比如不了解XML AUTO PATH,可以移步MSDN查询一下。
PS:XML Reader不是免费的,在数据量大的情况下性能等同于迭代;这种方式易让别人觉得人你有奇技淫巧,一下下逼格就提高了。
Set-Based Approach
USE AdventureWorks2014; GO DECLARE @myIDs NVARCHAR(MAX) = ''; SELECT TOP 10 @myIDs = @myIDs + N',' + CAST(SalesOrderID AS NVARCHAR) FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC SET @myIDs = STUFF(@myIDs, 1, 1, ''); SELECT @myIDs AS comma_separated_output;
分析:Set-Based 方式,不管是从性能上,还是从可读性上,都有很大的提升,也是小陈推荐的。
彩蛋
三种方式大体可以解决我们在T-SQL常见的问题,但有时候也会掉进坑里,有些也是动态列表不能解决的。
比如我们在写SP的时候,常把IDs作为参数传进去,然后在动态SQL中:orderid in @myIDs, 现实却是很骨感的……