SQL分组统计API调用次数异常：多账户重复计数问题求助

你的查询错误核心不是CASE与COUNT的组合，而是Accounts表的关联条件缺失：

• ApiCalled表记录的是「客户+账户」的API调用，但你关联Accounts时只匹配了CustomerId，没有匹配ApiCalled中的AccountId（这是符合业务逻辑的必要字段）。
• 这会导致每条ApiCalled记录与该客户下的所有账户生成笛卡尔积，最终分组统计时，每个账户都会统计到该客户所有账户的调用次数。

修正后的查询语句 #

SELECT 
    c.Name AS Customer, 
    sa.Name AS account, 
    COUNT(CASE cuec.EndpointCalled WHEN 0 THEN 1 ELSE NULL END) AS api1,
    COUNT(CASE cuec.EndpointCalled WHEN 1 THEN 1 ELSE NULL END) AS api2,
    COUNT(CASE cuec.EndpointCalled WHEN 2 THEN 1 ELSE NULL END) AS api3
FROM
    Customers.ApiCalled cuec
    INNER JOIN Customers.Customers c
        ON c.CustomerId = cuec.CustomerId
    INNER JOIN Customers.Accounts sa
        ON sa.CustomerId = cuec.CustomerId 
        AND sa.AccountId = cuec.AccountId -- 新增AccountId关联条件，匹配到对应的账户
GROUP BY
    c.Name, sa.Name

补充优化说明 #

你也可以用SUM替代COUNT，逻辑更直观（CASE返回1或0），统计效果完全相同：

SELECT 
    c.Name AS Customer, 
    sa.Name AS account, 
    SUM(CASE cuec.EndpointCalled WHEN 0 THEN 1 ELSE 0 END) AS api1,
    SUM(CASE cuec.EndpointCalled WHEN 1 THEN 1 ELSE 0 END) AS api2,
    SUM(CASE cuec.EndpointCalled WHEN 2 THEN 1 ELSE 0 END) AS api3
FROM
    Customers.ApiCalled cuec
    INNER JOIN Customers.Customers c
        ON c.CustomerId = cuec.CustomerId
    INNER JOIN Customers.Accounts sa
        ON sa.CustomerId = cuec.CustomerId 
        AND sa.AccountId = cuec.AccountId
GROUP BY
    c.Name, sa.Name

修正后，当客户cust1的两个账户各调用一次api1时，就能得到你预期的结果：

customer | account | api1 | api2 | api3
cust1    | acc1    | 1    |  0   |  0
cust1    | acc2    | 1    |  0   |  0

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