T-SQL：如何加入@variable 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6293211/
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: How to join @variable tables
提问by Wanderson
Possible Duplicate:
T-SQL: How to join @variable tables (another try)
First: I'm using SQL Server 2008. In a complex algorithm that involves a lot of data, I have been using a technical of creating intermediate table variables:
第一：我使用的是 SQL Server 2008。在涉及大量数据的复杂算法中，我一直在使用创建中间表变量的技术：
DECLARE @table AS TABLE (Col1 INT, Col2 VARCHAR(100))
Unfortunately, SQL Server does not support JOINning @variable tables, it is only allowed to join "true" tables, those in the database.
不幸的是，SQL Server 不支持 JOINning @variable 表，它只允许连接数据库中的“真实”表。
I could do the "manual" join, like
我可以做“手动”加入，比如
FROM @table1 t1, @table2 t2
WHERE t1.Id = t2.Id
This results in a INNER JOIN, but this is wrong for me. The question is: How do FULL JOIN two @variable tables?
这会导致 INNER JOIN，但这对我来说是错误的。问题是：如何完全连接两个@variable 表？
回答by Ray
What do you mean by SQL doesn't support Joining table variables?
SQL 不支持连接表变量是什么意思？
It works for me
这个对我有用
DECLARE @table1 AS TABLE (Col1 INT, Col2 VARCHAR(100))
DECLARE @table2 AS TABLE (Col1 INT, Col2 VARCHAR(100))
SELECT *
FROM @table1 t1
FULL JOIN @table2 t2 on t1.Col1 = t2.Col1
回答by Brett
You should be able to do a join using an @tableVariable
您应该能够使用 @tableVariable
SELECT *
FROM table1 t
FULL JOIN @tableVariable tv
ON (tv.col = cnc.col)
Could it have anything to do with your compatability setting? (mine is at 100)
它可能与您的兼容性设置有关吗？（我的是100）
sp_dbcmptlevel 'database_name'
ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }
回答by mattmc3
I'm not sure what you're asking, as joining works just fine for table variables. See this example:
我不确定你在问什么，因为加入对表变量来说效果很好。看这个例子：
declare @table as table (Col1 int, Col2 varchar(100))
declare @table2 as table (Col1 int, Col2 varchar(100))
insert into @table
select 1, 'A'
union all
select 1, 'C'
union all
select 1, 'D'
insert into @table2
select 2, 'A'
union all
select 2, 'B'
union all
select 2, 'D'
union all
select 2, 'E'
select
*
from
@table t1 full outer join
@table2 t2 on t1.Col2 = t2.Col2
select
*
from
@table t1 left join
@table2 t2 on t1.Col2 = t2.Col2
select
*
from
@table t1 right join
@table2 t2 on t1.Col2 = t2.Col2
select
*
from
@table t1 join
@table2 t2 on t1.Col2 = t2.Col2
