T-SQL，在一次选择中更新多个变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/469318/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
T-SQL, updating more than one variable in a single select
提问by jandersson
Is it possible to update more than one local variable in a single select?
是否可以在一次选择中更新多个局部变量？
Something like:
就像是：
set
@variableOne = avg(someColumn),
@variableTwo = avg(otherColumn)
from tblTable
It seems a bit wasteful to make two separate select operations for something as trivial as this task:
为像这个任务这样微不足道的事情做两个单独的选择操作似乎有点浪费：
set @variableOne = ( select avg(someColumn) from tblTable )
set @variableTwo = ( select avg(otherColumn) from tblTable )
回答by Frederik Gheysels
Something like this:
像这样的东西：
select @var1 = avg(someColumn), @var2 = avg(otherColumn)
from theTable
回答by Amy B
You can use SELECT assignment to assign multiple variables. This code generates a single row of constants and assigns each to a variable.
您可以使用 SELECT 赋值来分配多个变量。此代码生成单行常量并将每个常量分配给一个变量。
SELECT
@var1 = 1,
@var2 = 'Zeus'
You can even query tables and do assignment that way:
您甚至可以查询表并以这种方式进行分配：
SELECT
@var1 = c.Column1,
@var2 = c.Column2,
FROM
Customers c
WHERE c.CustomerID = @CustomerID
Beware: This code operates like a while loop.
当心：这段代码就像一个 while 循环。
- If there are multiple rows, each row will be assigned to the variables and the last row will be the one left in there. If you didn't specify an ordering, you gave up control over which row will be the last row.
- If there are no rows, the variables will not be assigned to at all. The variables will not be set to null - they will remain unchanged. This is a key problem if the assignment is done in a loop (typically resulting in an infinite loop as the variables never change).
- 如果有多行，每一行将被分配给变量，最后一行将是留在那里的行。如果您没有指定排序，您就放弃了对哪一行将是最后一行的控制。
- 如果没有行，则根本不会分配变量。变量不会被设置为空——它们将保持不变。如果分配是在循环中完成的，这是一个关键问题（通常会导致无限循环，因为变量永远不会改变）。
Prefer using SET assignment over SELECT assignment. Only use SELECT assignment when considering both scenarios above.
优先使用 SET 赋值而不是 SELECT 赋值。仅在考虑上述两种情况时才使用 SELECT 赋值。
回答by no_one
how about
怎么样
SELECT @variableOne = avg(someColumn), @variableTwo = avg(otherColumn) from tblTable
it works for me just fine.
它对我有用。
回答by Cesar Murrieta
SELECT DISTINCT
@Var1 = Column1,
@Var2 = Column2
FROM MyTable
WHERE Columnx = @Parm1
