sql - 如何将此 SQL 查询转换为 MS Access 查询？

Question

我在 SQL 中有查询

SELECT        COUNT(DISTINCT dbo.Polling_Stations.P_ID) AS [Male Stations]
FROM            dbo.Agent INNER JOIN
                     dbo.Polling_Stations ON dbo.Agent.P_ID = dbo.Polling_Stations.P_ID
GROUP BY dbo.Polling_Stations.Gender
HAVING        (dbo.Polling_Stations.Gender = N'Male')

我已将其转换为 Access:

SELECT        COUNT(DISTINCT Polling_Stations.P_ID) AS [Male Stations]
FROM            Agent INNER JOIN
                     Polling_Stations ON Agent.P_ID = Polling_Stations.P_ID
GROUP BY Polling_Stations.Gender
HAVING        (Polling_Stations.Gender = 'Male') 

但它给了我一个错误:
查询表达式“Count(DISTINCT Polling_Stations.P_ID)”中的语法错误(缺少运算符)。

Answer 1

Access SQL 不支持 COUNT(DISTINCT ...) ，所以你需要做

SELECT COUNT(*) AS [Male Stations]
FROM
(
    SELECT DISTINCT Polling_Stations.P_ID
    FROM Agent INNER JOIN Polling_Stations 
        ON Agent.P_ID = Polling_Stations.P_ID
    WHERE Polling_Stations.Gender = "Male"
)