如何使用高级Count统计团队客户及文档完成状态？

要同时统计团队的客户总数和客户的文档完成状态，你可以通过先计算每个客户的文档达标情况，再关联团队进行汇总来实现，以下是具体的SQL实现：

方式一：汇总每个团队的各状态客户数 #

这个查询会返回每个团队的总客户数、达标客户数和未达标客户数：

WITH customer_doc_status AS (
    -- 先计算每个客户的文档完成状态
    SELECT 
        c.ID AS customer_id,
        c.teamID,
        -- 客户拥有2份文档标记为complete，否则为incomplete
        CASE 
            WHEN COUNT(d.ID) = 2 THEN 'complete'
            ELSE 'incomplete'
        END AS doc_status
    FROM customer c
    -- 左连接文档表，确保没有文档的客户也被统计
    LEFT JOIN documents d ON c.ID = d.customerId
    GROUP BY c.ID, c.teamID
)
-- 关联团队表，汇总各状态数据
SELECT 
    t.Name AS team_name,
    COUNT(cds.customer_id) AS total_customers,
    -- 统计达标客户数量
    SUM(CASE WHEN cds.doc_status = 'complete' THEN 1 ELSE 0 END) AS complete_customers,
    -- 统计未达标客户数量
    SUM(CASE WHEN cds.doc_status = 'incomplete' THEN 1 ELSE 0 END) AS incomplete_customers
FROM team t
-- 左连接团队，确保没有客户的团队也会显示
LEFT JOIN customer_doc_status cds ON t.ID = cds.teamID
GROUP BY t.Name
ORDER BY t.Name;

方式二：查看每个团队下的客户明细及状态 #

如果需要查看每个团队下具体客户的文档状态，可以用这个查询：

SELECT 
    t.Name AS team_name,
    c.Name AS customer_name,
    CASE 
        WHEN COUNT(d.ID) = 2 THEN 'complete'
        ELSE 'incomplete'
    END AS doc_status
FROM team t
JOIN customer c ON t.ID = c.teamID
LEFT JOIN documents d ON c.ID = d.customerId
GROUP BY t.Name, c.Name
ORDER BY t.Name, c.Name;

注意事项 #

• 如果documents表中存在重复的documentID（比如同一文档重复录入），需要用COUNT(DISTINCT d.documentID)替代COUNT(d.ID)，避免重复统计文档数量。
• 用LEFT JOIN是为了确保没有文档的客户、没有客户的团队都能被纳入统计，如果你只需要统计有客户的团队，可以把LEFT JOIN换成INNER JOIN。

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