如何联接两个表,但仅返回不匹配的行? [英] 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
来完成:
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屋!