如何在MS Access中查询同一GroupId和RuleId下含10和42的条目

问题描述 #

我有一个从外部MS Access数据库（C:\Data\LinkedDatabases\ExternalData.mdb）链接而来的表MyTable，结构包含GroupId、RuleId和DecisionCode字段。需要创建查询，列出同一GroupId和RuleId下，DecisionCode同时存在10和42值的所有条目，仅当两个值都存在时才返回对应条目。

初始查询及错误 #

初始编写的查询出现语法错误：

SELECT A.*
FROM (SELECT * FROM MyTable IN 'C:\Data\LinkedDatabases\ExternalData.mdb') AS A
WHERE EXISTS (
SELECT 1
FROM (
    SELECT GroupId, RuleId
    FROM MyTable IN 'C:\Data\LinkedDatabases\ExternalData.mdb'
    WHERE DecisionCode IN (10, 42)
    GROUP BY GroupId, RuleId
    HAVING COUNT(DISTINCT DecisionCode) = 2
) AS B
WHERE A.GroupId = B.GroupId
AND A.RuleId = B.RuleId
)
AND A.DecisionCode IN (10, 42);

错误信息：missing operator in query expression 'COUNT(DISTINCT DecisionCode) = 2'
原因是MS Access的COUNT函数不支持DISTINCT参数。

修改后的查询问题 #

修改后的查询仍未返回正确结果：

SELECT A.*
FROM (SELECT * FROM MyTable IN 'C:\Data\LinkedDatabases\ExternalData.mdb') AS A
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT GroupId, RuleId
        FROM MyTable IN 'C:\Data\LinkedDatabases\ExternalData.mdb'
        WHERE DecisionCode IN (10, 42)
        GROUP BY GroupId, RuleId
        HAVING COUNT(*) = 2
    ) AS B
    WHERE A.GroupId = B.GroupId
    AND A.RuleId = B.RuleId
    AND EXISTS (
        SELECT 1
        FROM MyTable AS C
        WHERE C.GroupId = A.GroupId
        AND C.RuleId = A.RuleId
        AND C.DecisionCode = 10
    )
    AND EXISTS (
        SELECT 1
        FROM MyTable AS D
        WHERE D.GroupId = A.GroupId
        AND D.RuleId = A.RuleId
        AND D.DecisionCode = 42
    )
)
AND A.DecisionCode IN (10, 42);

该查询试图通过嵌套子查询确保同一GroupId和RuleId下同时存在10和42，但仍返回仅含10的条目。

示例数据 #

期望结果 #

正确解决方案 #

方法一：双重EXISTS子查询验证 #

直接通过两个EXISTS分别验证当前记录所属的GroupId+RuleId是否同时包含10和42，逻辑清晰且符合Access语法：

SELECT A.*
FROM (SELECT * FROM MyTable IN 'C:\Data\LinkedDatabases\ExternalData.mdb') AS A
WHERE A.DecisionCode IN (10, 42)
AND EXISTS (
    SELECT 1
    FROM MyTable IN 'C:\Data\LinkedDatabases\ExternalData.mdb' AS B
    WHERE B.GroupId = A.GroupId
    AND B.RuleId = A.RuleId
    AND B.DecisionCode = 10
)
AND EXISTS (
    SELECT 1
    FROM MyTable IN 'C:\Data\LinkedDatabases\ExternalData.mdb' AS C
    WHERE C.GroupId = A.GroupId
    AND C.RuleId = A.RuleId
    AND C.DecisionCode = 42
);

方法二：GROUP BY+HAVING替代COUNT(DISTINCT) #

利用IIF函数标记目标值的存在，通过SUM判断两个值是否都出现，替代COUNT(DISTINCT)的功能：

SELECT A.*
FROM (SELECT * FROM MyTable IN 'C:\Data\LinkedDatabases\ExternalData.mdb') AS A
INNER JOIN (
    SELECT GroupId, RuleId
    FROM MyTable IN 'C:\Data\LinkedDatabases\ExternalData.mdb'
    WHERE DecisionCode IN (10, 42)
    GROUP BY GroupId, RuleId
    HAVING SUM(IIF(DecisionCode=10,1,0))>0 AND SUM(IIF(DecisionCode=42,1,0))>0
) AS B ON A.GroupId = B.GroupId AND A.RuleId = B.RuleId
WHERE A.DecisionCode IN (10, 42);

两种方法均可正确返回期望结果，方法一更直观易维护，方法二则适合需要扩展更多判断条件的场景。

内容的提问来源于stack exchange，提问作者MK01111000