vba 如何在 MS Access 中交叉？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22347535/
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 to INTERSECT in MS Access?
提问by daZza
Somehow MS Access doesn't seem to have access to the keyword INTERSECT, which I need right now in order to fix some data errors in my database.
不知何故，MS Access 似乎无法访问关键字 INTERSECT，我现在需要它来修复我的数据库中的一些数据错误。
I had a look on older questions and found some answers that tried to solve this problem by using a select distinct [...] inner join [...] construction.
我查看了较旧的问题，并找到了一些试图通过使用 select distinct [...] 内连接 [...] 结构来解决此问题的答案。
However, this doesn't seem to help me, as the syntax doesn't apply to the table I need to intersect.
但是，这似乎对我没有帮助，因为语法不适用于我需要相交的表。
Here's a short explanation on what I want to achieve with the INTERSECT:
以下是我想通过 INTERSECT 实现的目标的简短说明：
My table looks like this (simplified):
我的表看起来像这样（简化）：
A | B
一个 | 乙
hello | world
你好 | 世界
world | hello
世界| 你好
Now these datasets are redundant, but unfortunately can't be caught by a constraint, as they are not exactly the same, but mirrored. That's why they got inserted in the first place...
现在这些数据集是多余的，但不幸的是不能被约束捕获，因为它们不完全相同，而是镜像。这就是为什么他们首先被插入......
I think normally I would be able to select all the affected data sets by using a
我认为通常我可以通过使用
SELECT A,B FROM tbl
INTERSECT
SELECT B,A from tbl
After having selected them I could just delete them and would be rid of that problem... Does anyone have an idea on how to implement this using MS Access?
选择它们后，我可以删除它们并解决这个问题......有没有人知道如何使用 MS Access 实现这一点？
回答by Gord Thompson
For test data in a table named [MirrorTest]
对于名为 [MirrorTest] 的表中的测试数据
pk A B
-- ----- -----
1 foo bar
2 hello world
3 hello there
4 world hello
5 bar baz
6 bar foo
the query
查询
SELECT pk, A, B
FROM MirrorTest
WHERE A<=B
UNION ALL
SELECT pk, B, A
FROM MirrorTest
WHERE A>B
will return all of the rows such that A<=B, i.e.,
将返回所有行，使得 A<=B，即，
pk A B
-- ----- -----
2 hello world
3 hello there
5 bar baz
6 bar foo
1 bar foo
4 hello world
Wrap that in an aggregation query to find the candidates for deletion, defined as the larger [pk] value where there are duplicates
将其包装在聚合查询中以查找要删除的候选对象，定义为存在重复项的较大 [pk] 值
SELECT Max(pk) AS pkToDelete
FROM
(
SELECT pk, A, B
FROM MirrorTest
WHERE A<=B
UNION ALL
SELECT pk, B, A
FROM MirrorTest
WHERE A>B
) AS u
GROUP BY A, B
HAVING COUNT(*) > 1
returns
返回
pkToDelete
----------
6
4
so you could just use that in the WHERE clause of a DELETE query
所以你可以在 DELETE 查询的 WHERE 子句中使用它
DELETE FROM MirrorTest
WHERE pk IN
(
SELECT Max(pk) AS pkToDelete
FROM
(
SELECT pk, A, B
FROM MirrorTest
WHERE A<=B
UNION ALL
SELECT pk, B, A
FROM MirrorTest
WHERE A>B
) AS u
GROUP BY A, B
HAVING COUNT(*) > 1
)
