blocks|key|1461154|text|它们是通过连接来完成的。传统的方式:)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1461155|对于INTERSECT，可以使用内部联接。非常简单。如果没有纯粹的一对一关系，只需要使用GROUP+BY或DISTINCT。否则，正如其他人所提到的，您可能会获得比预期更多的结果。|1461156|对于减号，您可以使用左连接并使用WHERE来限制它，这样您只能从主表中返回与左JOINed表不匹配的行。|1461157|很简单。|1461158|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

They're done through JOINs. The old fashioned way :)

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.

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.

Easy peasy.

blocks|key|1676358|text|不幸的是，MS+Access不支持减号--一种解决办法是创建三个查询，一个包含完整的数据集，一个提取要筛选出的行，第三个查询将两个表连接在一起，只提取仅存在于完整数据集中的记录。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1676359|INTERSECT也是如此，除了您将通过一个内部连接并且只返回存在于两者中的记录之外。|1676360|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

Same thing goes for INTERSECT, except you would be doing it via an inner join and only returning records that exist in both.

blocks|key|1461353|text|INTERSECT不是内联接。他们不一样。在INTERSECT不能执行的情况下，内联接将为您提供重复行。您可以通过以下方式获得相同的结果：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1461354|SELECT+DISTINCT+a.*
FROM+a
INNER+JOIN+b
+++on+a.PK+=+b.PK|code-block|syntax|javascript|1461355|注意主键必须是一个或多个主键列。如果表格上没有PK+(不好！)，你必须这样写：|1461356|SELECT+DISTINCT+a.*
FROM+a
INNER+JOIN+b
+++ON++a.Col1+=+b.Col1
+++AND+a.Col2+=+b.Col2
+++AND+a.Col3+=+b.Col3+...|1461357|使用MINUS可以做同样的事情，但是使用左连接和WHERE条件检查表b的一个不可为空的列(最好是主键)上的null。|1461358|SELECT+DISTINCT+a.*
FROM+a
LEFT+JOIN+b
+++on+a.PK+=+b.PK
WHERE+b.PK+IS+NULL|1461359|这应该就行了。|1461360|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

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:

<pre><code>SELECT DISTINCT a.*
FROM a
INNER JOIN b
 on a.PK = b.PK
</code></pre>

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:

<pre><code>SELECT DISTINCT a.*
FROM a
INNER JOIN b
 ON a.Col1 = b.Col1
 AND a.Col2 = b.Col2
 AND a.Col3 = b.Col3 ...
</code></pre>

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).

<pre><code>SELECT DISTINCT a.*
FROM a
LEFT JOIN b
 on a.PK = b.PK
WHERE b.PK IS NULL
</code></pre>

That should do it.

blocks|key|1461438|text|我相信这个做了减号|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1461439|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|code-block|syntax|javascript|1461440|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I believe this one does the MINUS

<pre><code>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
</code></pre>

blocks|key|1676561|text|Access中没有减号，但您可以使用子查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1676562|SELECT+DISTINCT+a.*
FROM+a
WHERE+a.PK+NOT+IN+(SELECT+DISTINCT+b.pk+FROM+b)|code-block|syntax|javascript|1676563|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

No MINUS in Access, but you can use a subquery.
<pre><code>SELECT DISTINCT a.*
FROM a
WHERE a.PK NOT IN (SELECT DISTINCT b.pk FROM b)
</code></pre>

I have researched and haven't found a way to run INTERSECT and MINUS operations in MS Access. Does any way exist

How can I implement SQL INTERSECT and MINUS operations in MS Access

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

我研究过，但还没有找到在MS Access中运行INTERSECT和MINUS操作的方法。有没有任何方式存在

问如何在MS Access中实现SQL INTERSECT和MINUS操作
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MS Access中实现SQL INTERSECT和MINUS操作EN