T-SQL Recipes之生成动态列表数据

时间:2021-05-01 21:33:23

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, 现实却是很骨感的……