gpt4 book ai didi

sql-server - 使用 SELECT 语句分配变量

转载 作者:行者123 更新时间:2023-12-04 02:56:48 24 4
gpt4 key购买 nike

你能解释一下奇怪的行为吗?

DECLARE @t VARCHAR(256) = ''

SELECT @t = @t + CAST(smb.symbol AS VARCHAR(256))
FROM (
SELECT 1,'7'
UNION ALL
SELECT 2,'8'
UNION all
SELECT 3,'9'
) AS smb(n, symbol)
ORDER BY n

SELECT @t

输出:

789

这对我来说没问题。

DECLARE @t VARCHAR(256) = ''

SELECT @t = @t + CAST(smb.symbol AS VARCHAR(256))
FROM (
SELECT NUMS.N-1 AS N, CHAR(N-1) AS symbol
FROM (
SELECT 1 + n.n1 + nn.n2 * 10 + nnn.n3 * 100 as N
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS n(n1)
CROSS JOIN (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS nn(n2)
CROSS JOIN (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS nnn(n3)
) AS NUMS
WHERE NUMS.N BETWEEN 56 AND 58
) AS smb(N, symbol)
ORDER BY smb.N

SELECT @t

输出:

9

那么为什么第二个例子只输出最后一个符号呢?

最佳答案

在使用多行变量赋值时不要依赖 order by。

例如试试这个:

DECLARE @c INT = 0

SELECT
@c = @c + x
FROM (VALUES(1),(2),(3)) AS src(x)
WHERE x BETWEEN 1 AND 3
ORDER BY 1 - x DESC

SELECT @c

SET @c = 0

SELECT
@c = @c + x
FROM (VALUES(1),(2),(3)) AS src(x)
WHERE x BETWEEN 1 AND 3
ORDER BY x DESC

SELECT @c

http://sqlmag.com/sql-server/multi-row-variable-assignment-and-order

关于sql-server - 使用 SELECT 语句分配变量,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/16519864/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com