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

Question

假设我有一个名为 #Foo 的表格：

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

还有表格 #Bar：

Value
-----
1
2.5

我想使用简单语句创建表Result并实现以下效果：

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

到目前为止，我发明的东西有：

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的更快或更标准的方法吗？

- Ryszard Dżegan

---

你的解决方案是我本应该采用的方法。 - Johan

"ZIP JOIN"是什么？这是什么意思？ - ypercubeᵀᴹ

好的，下面是翻译的结果：@ypercube 的回答：如果您在查询中使用了 GROUP BY 子句，则结果将按指定的列进行分组，并且每个组将只返回一个结果行。如果您想要为每个组选择多个行，则可以使用聚合函数（例如 MAX、MIN、SUM、AVG 等）来计算每个组的其他列的值。 - Ryszard Dżegan

由于关系模型通常不要求行顺序有意义，因此这似乎是一种适用范围相当有限的连接方式。如果你想追求性能，我不认为你会找到更快的方法。如果你想提高性能，可能更快的方法是从两个查询生成两个有序结果集，然后通过循环遍历结果来在应用程序中处理“连接”。 - Bacon Bits

Answer 1

你可以简单地尝试这个。

;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

- Mova

Answer 2

DECLARE @Foo TABLE (pk_id int identity(1,1), Id INT, Color VARCHAR(10));
DECLARE @Bar TABLE (pk_id int identity(1,1), Value DECIMAL(2, 1))

INSERT INTO @Foo (Id, Color)
VALUES (1, 'Red'), (2, 'Green'), (3, 'Blue'), (4, NULL)

INSERT INTO @Bar (Value)
VALUES (1), (2.5);

SELECT F.id, F.Color, B.Value
FROM @Foo F
    LEFT JOIN @Bar B ON F.pk_id = B.pk_id

- Bob Boroujerdi Far

Answer 3

这个可以吗？（诚然，我可能误解了问题）

SELECT
  F.ID AS ID,
  F.Color AS Color,
  B.Value AS Value
FROM #Foo F
  LEFT OUTER JOIN #Bar B ON F.ID = FLOOR(B.Value)

--this DOES seem to return the correct output, but I'm not sure that my logic
--is what you are after
SELECT
  F.ID AS ID,
  F.Color AS Color,
  B.Value AS Value
FROM 
    (
      VALUES
        (1,'Red'),(2,'Green'),(3,'Blue'),(4, NULL)
    ) AS F(ID, Color)
  LEFT OUTER JOIN 
    (
      VALUES    
        (1), (2.5)
    ) AS B(Value) 
  ON F.ID = FLOOR(B.Value)

或者您想要实现以下功能：

• 按ID对#Foo进行排序
• 按值对#Boo进行排序
• 匹配：
  • #Foo中的“第一”行与#Bar中的“第一”行
  • #Foo中的“第二”行与#Bar中的“第二”行
  • 等等...

（抱歉，我不熟悉“ZIP JOIN”是什么。不过我会查看@RszardDzegan提供的链接。）

- Steve Rowland

---

我需要将两个表粘合在一起，而不考虑它们的值。假设有两个结果集，并希望通过将它们设置在一起来创建一个统一的表，如下所示：B第一行紧接着A第一行，B第二行紧接着A第二行等等。在这种情况下，值是无关紧要的。只是在SQL中寻找一个简单的解决方案。 - Ryszard Dżegan

在这种情况下，我认为使用CTE可能是您最好的选择，只要注意性能即可。 我非常喜欢使用CTE，并在任何可以找到理由的地方使用它们，但是当CTE的结果集返回大量数据时，我遇到了性能问题。 “大量”通常意味着几百MB左右。唯一的其他问题是，在处理“数据集”时，“第一行”和“第二行”等究竟意味着什么？ 假设存在某些列可以逻辑排序行，那么另一个考虑因素就是如何处理“平局”。太棒了，这个问题很好！ - Steve Rowland

Answer 4

你可以尝试像这样做：

DECLARE @Foo TABLE (Id INT, Color VARCHAR(10));
DECLARE @Bar TABLE (Value DECIMAL(2, 1))

INSERT INTO @Foo (Id, Color)
VALUES (1, 'Red'), (2, 'Green'), (3, 'Blue'), (4, NULL)

INSERT INTO @Bar (Value)
VALUES (1), (2.5);

WITH ECROSS
AS (
    SELECT F.Id, F.Color, B.Value, DENSE_RANK() OVER (
            ORDER BY F.Id
            ) AS No1, DENSE_RANK() OVER (
            ORDER BY B.Value
            ) AS No2
    FROM @Foo F, @Bar B
    )
SELECT A.id, A.Color, B.Value
FROM ECROSS A
LEFT JOIN ECROSS B ON A.No1 = B.No2
    AND A.No1 = B.No1
GROUP BY A.id, A.Color, B.Value

- Casi

Answer 5

尝试以下代码。您只需要在同一结构中提供每个组的行号和数据类型即可。有了这个，您可以使用PIVOT运算符来生成预期的结果。

WITH 
CTE_FOO AS
(
   SELECT
       [Group]
      ,[Spread]
      ,[Aggregate]
   FROM 
      (VALUES
          (1, 1, N'Red'  )
         ,(2, 1, N'Green')
         ,(3, 1, N'Blue' )
         ,(4, 1, NULL    )
      ) AS FOO([Group], [Spread], [Aggregate])
),
CTE_BAR AS
(
   SELECT
       [Group]
      ,[Spread]
      ,CAST([Aggregate] AS nvarchar(max)) AS [Aggregate]
   FROM 
      (VALUES
          (1, 2, 1   )
         ,(2, 2, 2.5 )
      ) AS BAR([Group], [Spread], [Aggregate])
),
CTE_FOOBAR AS
(
   SELECT [Group], [Spread], [Aggregate] FROM CTE_FOO
   UNION ALL
   SELECT [Group], [Spread], [Aggregate] FROM CTE_BAR   
)
SELECT 
    [Group] AS [ID]
   ,[1]     AS [Color]
   ,[2]     AS [Value]
FROM
    CTE_FOOBAR
PIVOT
    (
        MAX([Aggregate]) FOR [Spread] IN ([1], [2])
    ) AS PivotTable

- Marcus Belz

Answer 6

您可以通过像这样的子查询来摆脱CTE或使您的查询更短

select Id,Color,Value from 
(
    SELECT [Id], [Color], ROW_NUMBER() OVER (ORDER BY [Id]) AS 'No'
    FROM #Foo
)x full outer join 
(
    SELECT [Value], ROW_NUMBER() OVER (ORDER BY [Value]) AS 'No'
    FROM #Bar
)y
on x.No=y.No

- vhadalgi

---

由于更改了连接类型，因此它“更优化”了。CTE始终由查询优化器内联，就像您手动执行的那样。 - usr

他们需要FULL JOIN，而不是LEFT JOIN。 - ypercubeᵀᴹ

oopss!! 是的，它们两个是一样的！也许这个更短？ - vhadalgi

我相信我更喜欢 CTE 版本，我觉得这个版本更易读。 - NickyvV

Answer 7

你可以跳过为 #Foo 创建新的行号，因为在这种情况下它的行号已经给定。

然后解决方案将变成：

SELECT F.Id,F.Color,newBar.Value from #Foo as F
LEFT JOIN
(
    SELECT [Value], ROW_NUMBER() OVER (ORDER BY [Value]) AS 'No'
    FROM #Bar
) newBar
on F.Id=newBar.No

这个解决方案已经经过测试和验证。它为您提供了所有 #Foo 的值，并且对于每个值，如果有的话，还提供了 #Bar 的排序值。

- Martijn van der Jagt

---

我同意在这种特定情况下，[Id]列充当行号，但这不是关键。我正在寻找一般解决方案。 - Ryszard Dżegan