此T-SQL代码中发生了什么？ （包含SELECT语句的结果） [英] What is happening in this T-SQL code? (Concatenting the results of a SELECT statement)

问题描述

我才刚刚开始学习T-SQL，并可以使用一些帮助来了解特定代码块中的情况。我在在上一个问题中收到的答案中修改了一些代码，下面是相关代码：

I'm just starting to learn T-SQL and could use some help in understanding what's going on in a particular block of code. I modified some code in an answer I received in a previous question, and here is the code in question:

DECLARE @column_list AS varchar(max) 
SELECT @column_list = COALESCE(@column_list, ',') + 
    'SUM(Case When Sku2=' + CONVERT(varchar, Sku2) + 
    ' Then Quantity Else 0 End) As [' + 
    CONVERT(varchar, Sku2) + ' - ' + 
    Convert(varchar,Description) +'],'
FROM OrderDetailDeliveryReview 
Inner Join InvMast on SKU2 = SKU and LocationTypeID=4
GROUP BY Sku2 , Description
ORDER BY Sku2 

Set @column_list = Left(@column_list,Len(@column_list)-1)

Select @column_list

----------------------------------------

1 row is returned:
,SUM(Case When Sku2=157 Then Quantity Else 0 End) As [157 -..., SUM(Case ...

T-SQL代码确实存在只是我想要的，即根据查询的结果生成单个结果，然后将其用于另一个查询。

The T-SQL code does exactly what I want, which is to make a single result based on the results of a query, which will then be used in another query.

但是，我不能通过将 SELECT @column_list = ... 放在 SELECT 语句。如果不分配给 @column_list ，则 SELECT 语句将简单地返回多行。通过在 SELECT 语句中包含变量，结果如何被展平为一个值？我应该如何阅读此T-SQL以正确了解正在发生的情况？

However, I can't figure out how the SELECT @column_list =... statement is putting multiple values into a single string of characters by being inside a SELECT statement. Without the assignment to @column_list, the SELECT statement would simply return multiple rows. How is it that by having the variable within the SELECT statement that the results get "flattened" down into one value? How should I read this T-SQL to properly understand what's going on?

推荐答案

在SQL Server中：

In SQL Server:

SELECT @var = @var + col
FROM TABLE

实际上是连接值。这是一种怪癖模式（目前我无法找到该功能文档的参考-在SQL Server社区中已经使用了多年）。如果@var在开始时为NULL（即未初始化的值），则您需要使用COALESCE或ISNULL（并且您将经常使用分隔符）：

actually concatenates the values. It's a quirks mode (and I am unable at this time to find a reference to the documentation of feature - which has been used for years in the SQL Server community). If @var is NULL at the start (i.e. an uninitialized value), then you need a COALESCE or ISNULL (and you'll often use a separator):

SELECT @var = ISNULL(@var, '') + col + '|'
FROM TABLE

或此命令以逗号分隔，然后仅删除前导逗号：

or this to make a comma-separated list, and then remove only the leading comma:

SELECT @var = ISNULL(@var, '') + ',' + col
FROM TABLE

SET @var = STUFF(@var, 1, 1, '')

或（由 KM ，依靠NULL +'，产生NULL以消除列表中第一项对STUFF的需要）：

or (courtesy of KM, relying on NULL + ',' yielding NULL to eliminate the need for STUFF for the first item in the list):

SELECT @var = ISNULL(@var + ',', '') + col
FROM TABLE 

或使用以下命令制作一个带有前导，分隔和结尾逗号的列表：

or this to make a list with a leading, separated and trailing comma:

SELECT @var = ISNULL(@var, ',') + col + ','
FROM TABLE

这篇关于此T-SQL代码中发生了什么？ （包含SELECT语句的结果）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！