如何将此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）'。

- sangeen

---

你的语法错误是什么？ - Kermit

查询表达式中的语法错误（缺少运算符）：“Count(DISTINCT Polling_Stations.P_ID)”。 - sangeen

方括号在访问中有效吗？ - Dan Bracuk

与你的问题无关，但性别过滤器应该在where子句中而不是having子句中。 - Dan Bracuk

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"
)

- Gord Thompson

Answer 2

Access不支持Count(DISTINCT ...)语句。在子查询中使用SELECT DISTINCT，并从父查询中进行计数。

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

- HansUp