sql - 来自 SQL 查询的奇怪行为

Question

我写了一个查询(这是该查询的最简单形式)

declare @tbl1 table(name varchar(50))
declare @tbl2 table(name varchar(50))
declare @query varchar(600)
set @query = ''

insert into @tbl1
select 'a' union select 'b'  union select 'c'  union select 'd'  union select 'e'

insert into @tbl2
select '1' union select '2'  union select '3'  union select '4'  union select '5'

select 
 @query = 
 ' Go ' + 
 @query +
 tbl1.name + ' (' +
 substring(
 (
    select ', ' + tbl2.name
    from @tbl2 as tbl2
    for xml path('')
 ), 3, 5000) + ') '
from @tbl1 as tbl1

print @query

我期待这样的结果

 GO a (1, 2, 3, 4, 5) GO  b (1, 2, 3, 4, 5)  GO c (1, 2, 3, 4, 5)  GO d (1, 2, 3, 4, 5)  GO e (1, 2, 3, 4, 5) 

但是这个平均查询返回给我

 Go  Go  Go  Go  Go a (1, 2, 3, 4, 5) b (1, 2, 3, 4, 5) c (1, 2, 3, 4, 5) d (1, 2, 3, 4, 5) e (1, 2, 3, 4, 5) 

有人可以向我解释一下这个结果吗？我不明白这一点。

Answer 1

你需要把它改成

select 
 @query = 
 @query +
 ' Go ' +    
 tbl1.name + ' (' +
 substring(
 (
    select ', ' + tbl2.name
    from @tbl2 as tbl2
    for xml path('')
 ), 3, 5000) + ') '
from @tbl1 as tbl1