为什么递归字符串连接中的子查询总是返回 NULL? [英] Why subquery inside recursive string concatenation always returns NULL?

问题描述

假设我有两个表:

• TABLE1:有我需要处理的 ID 列表.
• TABLE2:包含 ID 和值的键值对.

我需要从 TABLE2 中检索 TABLE1 中所有 ID 的值，作为单个字符串.为了实现这一点，我进行了下一个查询:

I need to retrieve the values from TABLE2 for all the IDs in TABLE1, as a single string. In order to achieve this, I made the next query:

DECLARE @id INT, @value VARCHAR(10);

SELECT @id=0, @value='';

SELECT
    @value = @value + (SELECT TOP 1 value FROM TABLE2 WHERE id=@id) + '-',
    @id = @id+1
FROM TABLE1

但是当我运行这个查询时，子查询总是返回 null(因此，在最后是 @value=NULL).我的问题是:为什么子查询 SELECT TOP 1 value FROM TABLE2 WHERE id=@id 总是返回 NULL，即使 ID 在 TABLE2 中找到?

But when I run this query, the subquery always returns null (and, therefore, @value=NULL at the end). My question is: Why does the subquery SELECT TOP 1 value FROM TABLE2 WHERE id=@id always returns NULL, even if the ID is found in TABLE2?

推荐答案

这是一种非常奇怪的尝试实现 treewalker 的方法，在 SELECT 中增加 @id 并期望它应用于子查询.它不起作用，因为 SQL Server 在查询设置阶段将子查询表达式的 @id 值锁定为常量.

This is a very weird way of trying to implement a treewalker, increasing @id in the SELECT and expecting it to apply to the subquery. It doesn't work because SQL Server locks the value of @id for the subquery expression as a constant right at the query setup phase.

看这个例子，其中返回的@value清楚地表明@id被锁定为1.对于你的问题，它被锁定为0，因此每个子查询将返回NULL，表面上是因为@id = 0没有匹配.

See this example, where the returned @value clearly indicates that @id is locked at 1. With your question, it was locked at 0, hence each subquery will return NULL, ostensibly because there is no match for @id = 0.

create table table1 (
  id int);
create table table2 (
  id int, value varchar(10));
insert table1 values (1),(2),(3),(4);
insert table2 values
(1,1),
(2,2),
(3,3),
(4,4);

DECLARE @id INT, @value VARCHAR(10);

SELECT @id=1, @value='';

SELECT
    @value = @value + (SELECT TOP 1 value FROM TABLE2 WHERE id=@id) + '-',
    @id = @id+1
FROM TABLE1;

select @value, @id

-- result
1-1-1-1       5

如果您只想要 2 中的值，那么您只需将子查询与 table.id 相关联，而不是变量 @id，如下所示:

If you merely wanted the values from 2, then instead of the variable @id, you just correlate the subquery to the table.id as below:

create table table1 (id int);
create table table2 (id int, value varchar(10));
insert table1 values (1),(2),(3),(4);
insert table2 values
(1,1),
(3,9),
(4,4);

DECLARE @value VARCHAR(10);

SELECT @value='';

SELECT
    @value = @value + isnull((SELECT TOP 1 value 
                              FROM TABLE2 
                              WHERE id=table1.id) + '-','')
FROM TABLE1;

select @value

-- Result
1-9-4

这篇关于为什么递归字符串连接中的子查询总是返回 NULL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！