如何计算Access中的唯一事件？

debugcn 发表于 Dev

礼条

我正在尝试使用来计算E_CUSTOMER_DETAIL_1.E_CUST_LOGIN_ID字段中事件的唯一数量count(distinct(E_CUSTOMER_DETAIL_1.E_CUST_LOGIN_ID))，但是在MS Access 2016中执行以下查询时出现错误。

如何计算E_CUSTOMER_DETAIL_1.E_CUST_LOGIN_ID现场的独特事件？

我在MS Access 2016中的查询（无区别）：

SELECT CUSTOMERS.E_CUST_LOGIN_ID, Count(E_CUSTOMER_DETAIL_1.E_CUST_LOGIN_ID)
FROM ((((CUSTOMERS 
INNER JOIN E_CUSTOMER_DETAIL ON CUSTOMERS.WH_CUST_NO = E_CUSTOMER_DETAIL.WH_CUST_NO) 
INNER JOIN E_CUST_E_CUST_REL ON CUSTOMERS.E_CUST_LOGIN_ID = E_CUST_E_CUST_REL.E_CUST_LOGIN_ID) 
INNER JOIN E_CUST_CHNL_USAGE_DETAIL ON E_CUST_E_CUST_REL.E_CUST_JOINT_OWN_LOGIN_ID = E_CUST_CHNL_USAGE_DETAIL.E_CUST_LOGIN_ID) 
INNER JOIN E_CUSTOMER_DETAIL AS E_CUSTOMER_DETAIL_1 ON E_CUST_E_CUST_REL.E_CUST_JOINT_OWN_LOGIN_ID = E_CUSTOMER_DETAIL_1.E_CUST_LOGIN_ID)
GROUP BY CUSTOMERS.E_CUST_LOGIN_ID;

专线小巴

MS Access不支持count(distinct)。解决此限制的一种方法是select distinct在子查询中进行聚合：

SELECT LOGIN_ID, Count(DETAIL_LOGIN_ID)
FROM (
    SELECT DISTINCT CUSTOMERS.E_CUST_LOGIN_ID AS LOGIN_ID, E_CUSTOMER_DETAIL_1.E_CUST_LOGIN_ID AS DETAIL_LOGIN_ID
    FROM ((((CUSTOMERS 
    INNER JOIN E_CUSTOMER_DETAIL ON CUSTOMERS.WH_CUST_NO = E_CUSTOMER_DETAIL.WH_CUST_NO) 
    INNER JOIN E_CUST_E_CUST_REL ON CUSTOMERS.E_CUST_LOGIN_ID = E_CUST_E_CUST_REL.E_CUST_LOGIN_ID) 
    INNER JOIN E_CUST_CHNL_USAGE_DETAIL ON E_CUST_E_CUST_REL.E_CUST_JOINT_OWN_LOGIN_ID = E_CUST_CHNL_USAGE_DETAIL.E_CUST_LOGIN_ID) 
    INNER JOIN E_CUSTOMER_DETAIL AS E_CUSTOMER_DETAIL_1 ON E_CUST_E_CUST_REL.E_CUST_JOINT_OWN_LOGIN_ID = E_CUSTOMER_DETAIL_1.E_CUST_LOGIN_ID)

)
GROUP BY LOGIN_ID;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。