T-SQL：如何在不创建额外行的情况下连接表 - 需要一行到一行的对应关系

发布于 2024-12-04 02:54:10 字数 704 浏览 3 评论 0原文

我有一个场景，我需要连接两个 SQL 表，并且正在努力解决如何做到这一点。假设表 AI 中有这样的内容：

ColA  ColB   ColC   ColD
45     55     17     45
45     55     17     47
45     55     17     49
45     55     17     51

在表 BI 中有这样的内容：

ColA  ColB   ColC   ColE
45     55     17     55
45     55     17     56
45     55     17     57
45     55     17     58

我需要创建一个如下所示的表 - 仅注意 4 行，而不是我使用的内部联接生成的 16 行。

ColA  ColB   ColC   ColD   ColE
45     55     17     45     55
45     55     17     47     56
45     55     17     49     57
45     55     17     51     58

我几乎没有主意了。两个表的行数始终相同。有没有办法使用连接来做到这一点，或者我应该迭代表A（即获取行号）并获取表B中的匹配行号？

任何想法将不胜感激。

原文

I have a scenario where I need to join two SQL tables and am struggling with how to do it.
Let's say in table A I have this:

ColA  ColB   ColC   ColD
45     55     17     45
45     55     17     47
45     55     17     49
45     55     17     51

And in Table B I have this:

ColA  ColB   ColC   ColE
45     55     17     55
45     55     17     56
45     55     17     57
45     55     17     58

I need to create a table that looks like this - note only the 4 rows, not the 16 that the inner join I was using produces.

ColA  ColB   ColC   ColD   ColE
45     55     17     45     55
45     55     17     47     56
45     55     17     49     57
45     55     17     51     58

I'm pretty much out of ideas. Both tables will always have the same number of rows. Is there a way to do this using joins, or should I iterate through the table A (ie get the row number) and get the matching row number in Table B?

Any thoughts would be greatly appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

故事↓在人 2024-12-11 02:54:10

假设至少是 SQL Server 2005

WITH T1
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY ColA, ColB, ColC ORDER BY ColD)
                RN
         FROM   TableA),
     T2
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY ColA, ColB, ColC ORDER BY ColE)
                RN
         FROM   TableB)
SELECT T1.ColA,
       T1.ColB,
       T1.ColC,
       T1.ColD,
       T2.ColE
FROM   T1
       JOIN T2
         ON T1.ColA = T2.ColA
            AND T1.ColB = T2.ColB
            AND T1.ColC = T2.ColC
            AND T1.RN = T2.RN

Assuming at least SQL Server 2005

WITH T1
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY ColA, ColB, ColC ORDER BY ColD)
                RN
         FROM   TableA),
     T2
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY ColA, ColB, ColC ORDER BY ColE)
                RN
         FROM   TableB)
SELECT T1.ColA,
       T1.ColB,
       T1.ColC,
       T1.ColD,
       T2.ColE
FROM   T1
       JOIN T2
         ON T1.ColA = T2.ColA
            AND T1.ColB = T2.ColB
            AND T1.ColC = T2.ColC
            AND T1.RN = T2.RN

回复收藏 0 原文

~没有更多了~