如何将查询含男女子女监护人的MS SQL转换为Access SQL？

嘿，我来帮你搞定这个Access SQL的转换问题！先把背景和需求理清楚：我们需要从tblGuardian和tblChild这两张表里，筛选出至少有一个男孩和一个女孩的监护人，然后返回他们的信息以及所有子女的出生日期，结果要和指定示例格式一致。

表结构及现有数据 #

tblGuardian #

tblChild #

预期结果格式 #

原MS SQL查询（Access不兼容） #

你提供的MS SQL查询逻辑是对的，但Access不支持COUNT(DISTINCT)语法，所以得调整：

SELECT g1.FirstName, g1.LastName, c1.FirstName, c1.DOB 
FROM tblGuardian g1 
INNER JOIN tblChild c1 ON g1.ID = c1.GuardianID 
WHERE g1.ID in ( 
    SELECT g.ID 
    FROM tblGuardian g 
    INNER JOIN tblChild c ON c.GuardianID = g.ID 
    GROUP BY g.ID 
    HAVING Count(Distinct c.Sex) = 2 
)

Access兼容的两种解决方案 #

方案1：使用INTERSECT（适合Access 2010及以上版本） #

这个方法更简洁，用INTERSECT取“有男孩的监护人”和“有女孩的监护人”的交集，完美替代COUNT(DISTINCT)的逻辑：

SELECT g.FirstName, g.LastName, c.FirstName AS [Child FirstName], 
       Format(c.DOB, 'yyyy-mm-dd') AS DOB
FROM tblGuardian g
INNER JOIN tblChild c ON g.ID = c.GuardianID
WHERE g.ID IN (
    -- 取同时有男孩和女孩的监护人ID
    SELECT GuardianID FROM tblChild WHERE Sex = 'M'
    INTERSECT
    SELECT GuardianID FROM tblChild WHERE Sex = 'F'
)
ORDER BY g.LastName, g.FirstName, c.DOB DESC;

细节说明：

• Format(c.DOB, 'yyyy-mm-dd')：把带时间的DOB字段转换成示例里的纯日期格式
• ORDER BY：让结果和示例的排序保持一致

方案2：使用EXISTS（兼容所有Access版本） #

如果你的Access版本比较旧，不支持INTERSECT，可以用EXISTS分别检查每个监护人是否同时有男孩和女孩：

SELECT g.FirstName, g.LastName, c.FirstName AS [Child FirstName], 
       Format(c.DOB, 'yyyy-mm-dd') AS DOB
FROM tblGuardian g
INNER JOIN tblChild c ON g.ID = c.GuardianID
WHERE g.ID IN (
    SELECT c1.GuardianID
    FROM tblChild c1
    WHERE EXISTS (
        -- 检查该监护人是否有女孩
        SELECT 1 FROM tblChild c2 
        WHERE c2.GuardianID = c1.GuardianID AND c2.Sex = 'F'
    )
    AND EXISTS (
        -- 检查该监护人是否有男孩
        SELECT 1 FROM tblChild c2 
        WHERE c2.GuardianID = c1.GuardianID AND c2.Sex = 'M'
    )
    GROUP BY c1.GuardianID
)
ORDER BY g.LastName, g.FirstName, c.DOB DESC;

这两个方案都能得到你想要的结果，根据你的Access版本选就行~

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