如何在 T-SQL 中实现 ZIP JOIN?

问题描述

假设我有桌子#Foo:

Let say I have table #Foo:

Id Color
-- ----
1  Red
2  Green
3  Blue
4  NULL

和表#Bar:

Value
-----
1
2.5

我想使用简单的语句来创建表结果:

I would like to create table Result using simple statement to get:

Id Color   Value
-- ----   -----
1  Red    1
2  Green  2.5
3  Blue   NULL
4  NULL   NULL

到目前为止我发明的是:

What I have invented so far is:

WITH cte1
AS
(
    SELECT [Id], [Color], ROW_NUMBER() OVER (ORDER BY [Id]) AS 'No'
    FROM #Foo
),
cte2
AS
(
    SELECT [Value], ROW_NUMBER() OVER (ORDER BY [Value]) AS 'No'
    FROM #Bar
)
SELECT [Id], [Color], [Value]
FROM cte1 c1
FULL OUTER JOIN cte2 c2 ON c1.[No] = c2.[No]

您知道在 T-SQL 中执行 ZIP JOIN 更快或更标准的方法吗?

Do you know faster or more standard way to do ZIP JOIN in T-SQL?

推荐答案

你可以试试这个.

;WITH CTE AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Id, Value FROM #Bar
)
SELECT F.Id, F.Color, CTE.Value
FROM #Foo F
LEFT JOIN CTE ON CTE.Id = F.Id