如何在MS Access中进行交互? [英] How to INTERSECT in MS Access?

问题描述

某种程度上，MS Access似乎没有访问关键字INTERSECT的权限，我现在需要用它来修复数据库中的某些数据错误.

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.

我看了一些较早的问题，并找到了一些答案，这些问题试图通过使用选择的不同的内部联接构造来解决此问题.

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.

但是，这似乎对我没有帮助，因为语法不适用于我需要相交的表.

However, this doesn't seem to help me, as the syntax doesn't apply to the table I need to intersect.

以下是我想通过INTERSECT实现的简短解释:

Here's a short explanation on what I want to achieve with the INTERSECT:

我的桌子看起来像这样(简化):

My table looks like this (simplified):

A | B

您好|世界

世界|你好

现在，这些数据集是多余的，但不幸的是，由于它们并不完全相同，而是被镜像了，因此无法被约束捕获.这就是为什么他们首先被插入的原因...

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

我认为通常我可以使用

 SELECT A,B FROM tbl
 INTERSECT
 SELECT B,A from tbl

选择了它们之后，我可以删除它们，并且摆脱这个问题...有人对如何使用MS Access实施此想法有想法吗?

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?

推荐答案

用于名为[MirrorTest]的表中的测试数据

For test data in a table named [MirrorTest]

pk  A      B    
--  -----  -----
 1  foo    bar  
 2  hello  world
 3  hello  there
 4  world  hello
 5  bar    baz  
 6  bar    foo  

查询

    SELECT pk, A, B
    FROM MirrorTest
    WHERE A<=B
UNION ALL
    SELECT pk, B, A
    FROM MirrorTest
    WHERE A>B

将返回所有行，以使A< = B，即

will return all of the rows such that A<=B, i.e.,

pk  A      B    
--  -----  -----
 2  hello  world
 3  hello  there
 5  bar    baz  
 6  bar    foo  
 1  bar    foo  
 4  hello  world

在聚合查询中将其换行以找到要删除的候选者，定义为存在重复项的较大[pk]值

Wrap that in an aggregation query to find the candidates for deletion, defined as the larger [pk] value where there are duplicates

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

返回

pkToDelete
----------
         6
         4

所以您可以在DELETE查询的WHERE子句中使用它

so you could just use that in the WHERE clause of a DELETE query

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
    )

这篇关于如何在MS Access中进行交互?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！