SQL Server：内部联接中使用的表变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2189551/
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 Server: table variable used in a inner join
提问by Ricky
What is the problem with following SQL. Can table variable not be used in JOIN clause?
以下 SQL 有什么问题。JOIN 子句中可以不使用表变量吗？
Error msg is of "Msg 170, Level 15, State 1, Line 8 Line 8: Incorrect syntax near 't1'."
错误消息是“消息 170，级别 15，状态 1，第 8 行第 8 行：'t1' 附近的语法不正确。”
Declare @t TABLE (
_SportName varchar(50),
_Lang varchar(3)
)
insert @t VALUES('Basketball', 'ENG') -- ENG
UPDATE tblSport t1
SET
t1.SportName = @t._SportName
FROM
@t INNER JOIN tblSport ON (t1.Lang = @t._Lang)
Thanks.
谢谢。
回答by Justin Niessner
Change your last statement to:
将您的最后一条语句更改为：
UPDATE t1, temp
SET t1.SportName = temp._SportName
FROM tblSport AS t1
INNER JOIN @t AS temp
ON t1.Lang = temp._Lang
(need to check exact syntax)
（需要检查确切的语法）
回答by takrl
Apart from the t1 alias being in the wrong place, nobody else mentioned using square brackets around the table variable, instead of an alias. Changing the update statement to the following will work too:
除了 t1 别名在错误的位置之外，没有其他人提到在表变量周围使用方括号，而不是别名。将更新语句更改为以下内容也将起作用：
UPDATE t1
SET
t1.SportName = [@t]._SportName
FROM
@t INNER JOIN tblSport t1 ON t1.Lang = [@t]._Lang
[Tested on SQL Server 2005.]
[在 SQL Server 2005 上测试。]
回答by Matt Whitfield
Justin's answer is correct syntactically - you need to assign an alias to the temp table (same for table type variables in 2008).
贾斯汀的答案在语法上是正确的 - 您需要为临时表分配一个别名（与 2008 年的表类型变量相同）。
However, be aware that neither table variables nor table-type variables have any statistics associated with them, and therefore can lead the query optimiser to make very dubious choices with regard to execution plans (because it will always estimate that the table variable contains 1 row - and therefore usually chooses nested loops as a join operator).
但是，请注意，表变量和表类型变量都没有与之关联的任何统计信息，因此会导致查询优化器对执行计划做出非常可疑的选择（因为它总是估计表变量包含 1 行） - 因此通常选择嵌套循环作为连接运算符）。
回答by Serjik
don't forget use aliasfor variable tables
不要忘记alias用于变量表
Declare @t TABLE (
_SportName varchar(50),
_Lang varchar(3)
)
insert @t VALUES('Basketball', 'ENG') -- ENG
UPDATE t1
SET
t1.SportName = t2._SportName
FROM tblSport t1 INNER JOIN
@t as t2 ON (t1.Lang = t2._Lang)
回答by gbn
Your alias t1is in the wrong place
你的别名t1在错误的地方
UPDATE
t1
SET
SportName = @t._SportName
FROM
@t INNER JOIN tblSport t1 ON (t1.Lang = @t._Lang)
