使用SELECT的SQL Server变量分配中的执行顺序 [英] Order of execution in SQL Server variable assignment using SELECT

问题描述

给出以下示例:

declare @i int
select @i = 1, @i = 2
select @i

@i总是2吗?

这是我能想到的最简单的例子，但是我正在考虑使用它来交换变量中的值.我还相信这种分配(选择)方法不符合ANSI(但是很有用)，但是在这种情况下，并不十分在意是否具有可移植代码.

This is about the most trivial example I can think of, but I am considering using this for swapping values in variables. I also believe this method of assignment (select) is not ANSI compliant (however useful), but don't really care to have portable code in this case.

更新

感谢@MichaelFredrickson，我们拥有@MartinSmith的答案，并引用了

Thanks to @MichaelFredrickson, we have @MartinSmith's answer and reference to MSDN on this. I am now struggling with what the second sentence in this documentation means, exactly (emphasis added):

如果单个SELECT语句中有多个赋值子句，则SQL Server不保证表达式的求值顺序. 请注意，只有在分配之间存在引用时，效果才可见.

但是，第一句话足以使我摆脱对行为的依赖.

The first sentence is plenty enough to keep me away from relying upon the behavior, however.

推荐答案

对于变量赋值， Martin Smith在这里回答此问题引用 MSDN :

如果单个SELECT语句中有多个赋值子句， SQL Server不保证评估的顺序 表达式.请注意，只有在存在以下情况时，效果才可见 作业中的参考.

If there are multiple assignment clauses in a single SELECT statement, SQL Server does not guarantee the order of evaluation of the expressions. Note that effects are only visible if there are references among the assignments.

但是...

如果我们要处理的是表格而不是变量，那是另一回事了.

But...

If we're dealing with tables, instead of with variables, it is a different story.

在这种情况下，如这个概念指出，在相同逻辑阶段中的所有表达式的求值就像是在同一时间点……不管它们从左到右的位置.

This concept states that all expressions in the same logical phase are evaluated as if the same point in time... regardless of their left-to-right position.

因此，当处理相应的UPDATE语句时:

So when dealing with the corresponding UPDATE statement:

DECLARE @Test TABLE (
    i INT,
    j INT
)

INSERT INTO @Test VALUES (0, 0)

UPDATE @Test
SET
    i = i + 10,
    j = i

SELECT 
    i, 
    j 
FROM 
    @Test

我们得到以下结果:

i           j
----------- -----------
10          0   

并且通过使用一次全部评估...在Sql Server中，您可以在没有中间变量/列的情况下交换表中的列值.

And by using the All-At-Once evaluation... in Sql Server you can swap column values in a table without an intermediate variable / column.

据我所知，大多数RBDMS的行为，但MySql是个例外.

Most RBDMSs behave this way as far as I know, but MySql is an exception.

请注意，只有在 作业.

Note that effects are only visible if there are references among the assignments.

我的理解是，如果您有SELECT语句，例如:

I understand this to mean that if you have a SELECT statement such as the following:

SELECT
    @A = ColumnA,
    @B = ColumnB,
    @C = ColumnC
FROM MyTable

然后，分配的执行顺序无关紧要……无论如何，您都将获得相同的结果.但是，如果您有类似的东西...

Then it doesn't matter what order the assignments are performed in... you'll get the same results no matter what. But if you have something like...

SELECT
    @A = ColumnA,
    @B = @A,
    @C = ColumnC
FROM MyTable

现在在分配中有一个引用(@B = @A)，现在分配@A和@B的顺序很重要.

There is now a reference among the assignments (@B = @A), and the order that @A and @B are assigned now matters.

这篇关于使用SELECT的SQL Server变量分配中的执行顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！