如何在同一条SQL查询语句中实现两个不同的count计数统计

解法 #

你可以从公司主表companies_company出发，两次左联关联表company_companyconnection分别统计双向计数，通用所有数据库的写法如下：

SELECT 
    c.id AS company_id,
    c.name AS company,
    COUNT(ccc_sup.dealer_id) AS as_supplier_count,
    COUNT(ccc_deal.supplier_id) AS as_dealer_count
FROM companies_company c
LEFT JOIN company_companyconnection ccc_sup 
    ON c.id = ccc_sup.supplier_id
LEFT JOIN company_companyconnection ccc_deal 
    ON c.id = ccc_deal.dealer_id
GROUP BY c.id, c.name
ORDER BY c.id ASC;

逻辑说明 #

1. 以公司主表为基础，可以保证所有注册公司都出现在结果中，不会遗漏没有关联记录的公司
2. 第一次左联company_companyconnection取该公司作为供应商的所有关联记录，统计非空的dealer_id数量就是作为供应商的关联数
3. 第二次左联company_companyconnection取该公司作为经销商的所有关联记录，统计非空的supplier_id数量就是作为经销商的关联数

特殊场景优化 #

如果你的company_companyconnection表存在重复的(supplier_id, dealer_id)组合，需要去重计数的话，可以加DISTINCT：

SELECT 
    c.id AS company_id,
    c.name AS company,
    COUNT(DISTINCT ccc_sup.dealer_id) AS as_supplier_count,
    COUNT(DISTINCT ccc_deal.supplier_id) AS as_dealer_count
FROM companies_company c
LEFT JOIN company_companyconnection ccc_sup 
    ON c.id = ccc_sup.supplier_id
LEFT JOIN company_companyconnection ccc_deal 
    ON c.id = ccc_deal.dealer_id
GROUP BY c.id, c.name
ORDER BY c.id ASC;

如果你使用PostgreSQL数据库，还可以用FILTER语法简化写法：

SELECT 
    c.id AS company_id,
    c.name AS company,
    COUNT(*) FILTER (WHERE ccc_sup.dealer_id IS NOT NULL) AS as_supplier_count,
    COUNT(*) FILTER (WHERE ccc_deal.supplier_id IS NOT NULL) AS as_dealer_count
FROM companies_company c
LEFT JOIN company_companyconnection ccc_sup ON c.id = ccc_sup.supplier_id
LEFT JOIN company_companyconnection ccc_deal ON c.id = ccc_deal.dealer_id
GROUP BY c.id, c.name
ORDER BY c.id;

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