SQL SELECT 多列 INTO 多变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1340775/
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
SQL SELECT multi-columns INTO multi-variable
提问by ala
I'm converting SQL from Teradata to SQL Server
我正在将 SQL 从 Teradata 转换为 SQL Server
in Teradata, they have the format
在 Teradata 中，它们具有格式
SELECT col1, col2
FROM table1
INTO @variable1, @variable2
In SQL Server, I found
在 SQL Server 中，我发现
SET @variable1 = (
SELECT col1
FROM table1
);
That only allows a single column/variable per statement. How to assign 2 or more variables using a single SELECT statement?
每个语句只允许一个列/变量。如何使用单个 SELECT 语句分配 2 个或更多变量？
回答by David M
SELECT @variable1 = col1, @variable2 = col2
FROM table1
回答by Svetlozar Angelov
SELECT @var = col1,
@var2 = col2
FROM Table
Here is some interesting information about SET / SELECT
这里有一些关于 SET / SELECT 的有趣信息
- SET is the ANSI standard for variable assignment, SELECT is not.
- SET can only assign one variable at a time, SELECT can make multiple assignments at once.
- If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise an error. SELECT will assign one of the values to the variable and hide the fact that multiple values were returned (so you'd likely never know why something was going wrong elsewhere - have fun troubleshooting that one)
- When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all (so the variable will not be changed from it's previous value)
- As far as speed differences - there are no direct differences between SET and SELECT. However SELECT's ability to make multiple assignments in one shot does give it a slight speed advantage over SET.
- SET 是变量赋值的 ANSI 标准，SELECT 不是。
- SET 一次只能赋值一个变量，SELECT 可以一次赋值多个。
- 如果从查询分配，SET 只能分配一个标量值。如果查询返回多个值/行，则 SET 将引发错误。SELECT 会将其中一个值分配给变量并隐藏返回多个值的事实（因此您可能永远不会知道为什么其他地方出了问题 - 解决那个问题很有趣）
- 从查询分配时，如果没有返回值，则 SET 将分配 NULL，其中 SELECT 根本不会进行分配（因此变量不会从其先前的值更改）
- 至于速度差异 - SET 和 SELECT 之间没有直接差异。然而，SELECT 能够一次性完成多个任务，这确实使它比 SET 具有轻微的速度优势。
