如何在 MS Access 中实现 SQL INTERSECT 和 MINUS 操作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/337158/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I implement SQL INTERSECT and MINUS operations in MS Access
提问by Varun Mahajan
I have researched and haven't found a way to run INTERSECT and MINUS operations in MS Access. Does any way exist
我已经研究过但还没有找到在 MS Access 中运行 INTERSECT 和 MINUS 操作的方法。有没有办法
回答by LeppyR64
INTERSECT is an inner join. MINUS is an outer join, where you choose only the records that don't exist in the other table.
INTERSECT 是内连接。MINUS 是一个外部连接，您只选择其他表中不存在的记录。
INTERSECT相交
select distinct
a.*
from
a
inner join b on a.id = b.id
MINUS
减
select distinct
a.*
from
a
left outer join b on a.id = b.id
where
b.id is null
If you edit your original question and post some sample data then an example can be given.
如果您编辑原始问题并发布一些示例数据，则可以给出一个示例。
EDIT: Forgot to add in the distinct to the queries.
编辑：忘记添加不同的查询。
回答by Dave Markle
INTERSECT is NOT an INNER JOIN. They're different. An INNER JOIN will give you duplicate rows in cases where INTERSECT WILL not. You can get equivalent results by:
INTERSECT 不是内连接。他们是不同的。在 INTERSECT 不会的情况下， INNER JOIN 将为您提供重复的行。您可以通过以下方式获得等效结果：
SELECT DISTINCT a.*
FROM a
INNER JOIN b
on a.PK = b.PK
Note that PK must be the primary key column or columns. If there is no PK on the table (BAD!), you must write it like so:
请注意，PK 必须是主键列或列。如果桌子上没有PK（糟糕！），你必须这样写：
SELECT DISTINCT a.*
FROM a
INNER JOIN b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
AND a.Col3 = b.Col3 ...
With MINUS, you can do the same thing, but with a LEFT JOIN, and a WHERE condition checking for null on one of table b's non-nullable columns (preferably the primary key).
使用 MINUS，您可以做同样的事情，但使用 LEFT JOIN 和 WHERE 条件检查表 b 的不可空列之一（最好是主键）上是否为空。
SELECT DISTINCT a.*
FROM a
LEFT JOIN b
on a.PK = b.PK
WHERE b.PK IS NULL
That should do it.
那应该这样做。
回答by Kevin Fairchild
They're done through JOINs. The old fashioned way :)
它们是通过 JOIN 完成的。老式的方式:)
For INTERSECT, you can use an INNER JOIN. Pretty straightforward. Just need to use a GROUP BY or DISTINCT if you have don't have a pure one-to-one relationship going on. Otherwise, as others had mentioned, you can get more results than you'd expect.
对于 INTERSECT，您可以使用 INNER JOIN。很简单。如果您没有纯粹的一对一关系，只需要使用 GROUP BY 或 DISTINCT。否则，正如其他人所提到的，您可以获得比预期更多的结果。
For MINUS, you can use a LEFT JOIN and use the WHERE to limit it so you're only getting back rows from your main table that don't have a match with the LEFT JOINed table.
对于 MINUS，您可以使用 LEFT JOIN 并使用 WHERE 来限制它，这样您只能从主表中取回与 LEFT JOINed 表不匹配的行。
Easy peasy.
十分简单。
回答by Patrick Harrington
Unfortunately MINUS is not supported in MS Access - one workaround would be to create three queries, one with the full dataset, one that pulls the rows you want to filter out, and a third that left joins the two tables and only pulls records that only exist in your full dataset.
不幸的是，MS Access 中不支持 MINUS - 一种解决方法是创建三个查询，一个具有完整数据集，一个用于提取要过滤掉的行，第三个用于连接两个表并仅提取仅提取的记录存在于您的完整数据集中。
Same thing goes for INTERSECT, except you would be doing it via an inner join and only returning records that exist in both.
同样的事情也适用于 INTERSECT，除了您将通过内部连接进行操作并且只返回存在于两者中的记录。
回答by Porebo
I believe this one does the MINUS
我相信这个做减号
SELECT DISTINCT
a.CustomerID,
b.CustomerID
FROM
tblCustomers a
LEFT JOIN
[Copy Of tblCustomers] b
ON
a.CustomerID = b.CustomerID
WHERE
b.CustomerID IS NULL
