如何连接两个表但只返回不匹配的行? [英] How can I join two tables but only return rows that don't match?

问题描述

我有两个看起来像这样的表:

I have two tables which look like this:

T1:  ID  |  Date  |  Hour  | Interval
T2:  ID  |  Date  |  Hour

我基本上需要在它们的 ID、日期和时间匹配时加入这些表.但是，我只想返回表 1 中与表 2 中的结果不匹配的结果.

I basically need to join these tables when their IDs, dates, and hours match. However, I only want to return the results from table 1 that do not match up with the results in table 2.

我知道这看起来很简单，但我遇到的问题是表 1 中有多行与表 2 匹配(任何给定的小时都有多个间隔).我需要返回所有这些间隔，只要它们不在表 2 中的同一小时内.

I know this seems simple, but where I'm stuck is the fact that there are multiple rows in table 1 that match up with table 2 (there are multiple intervals for any given hour). I need to return all of these intervals so long as they do not fall within the same hour period in table 2.

示例数据:

T1:  1  |  1/1/2011  |  1  |  1
     1  |  1/1/2011  |  1  |  2
     1  |  1/1/2011  |  2  |  1
     1  |  1/1/2011  |  2  |  2

T2:  1  |  1/1/2011  |  1

我的预期结果集将是 T1 的最后两行.任何人都可以指出我在正确的轨道上吗?

My expected result set for this would be the last two rows from T1. Can anyone point me on the right track?

推荐答案

SELECT T1.*
    FROM T1
    WHERE NOT EXISTS(SELECT NULL
                         FROM T2
                         WHERE T1.ID = T2.ID 
                             AND T1.Date = T2.Date
                             AND T1.Hour = T2.Hour)

也可以使用 LEFT JOIN 来完成:

It could also be done with a LEFT JOIN:

SELECT T1.*
    FROM T1
        LEFT JOIN T2
            ON T1.ID = T2.ID
                AND T1.Date = T2.Date
                AND T1.Hour = T2.Hour
    WHERE T2.ID IS NULL

这篇关于如何连接两个表但只返回不匹配的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！