如何统计客户ID在班次表两列中的出现总次数？

现有表结构 #

customers表 #

shifts表 #

预期输出 #

需求 #

统计customers.id在shifts表的id1或id2列中的出现总次数（两列均可为null），需包含所有客户，未出现的客户次数记为0。

已尝试的方法 #

1. 单列统计查询（以id1为例）：

SELECT t1.name, COUNT(t2.id1) AS num
FROM customers t1
INNER JOIN shifts t2
ON t2.id1=t1.id
WHERE t2.id1=t1.id
GROUP BY t2.id1

对id2可执行类似查询，但无法将两个结果求和。

2. 尝试直接用COUNT(t2.id1)+COUNT(t2.id2)，但因JOIN和GROUP子句的限制，无法得到正确结果。

3. 尝试合并两个子查询求和，但因WHERE子句要求name同时存在于两个子查询结果中，导致无返回；移除WHERE则结果错误：

SELECT q1.name, q1.num+q2.num
FROM 
    (SELECT t1.name, COUNT(t2.id1) AS num
    FROM customers t1
    INNER JOIN shifts t2
    ON t2.id1=t1.id
    WHERE t2.id1=t1.id
    GROUP BY t2.id1) AS q1,

    (SELECT t1.name, COUNT(t2.id2) AS num
    FROM customers t1
    INNER JOIN shifts t2
    ON t2.id2=t1.id
    WHERE t2.id2=t1.id
    GROUP BY t2.id2) AS q2
WHERE q1.name=q2.name

可行解决方案 #

方法一：LEFT JOIN结合条件统计 #

通过LEFT JOIN关联所有客户，用CASE语句分别统计id1和id2中的匹配次数，再求和：

SELECT 
    t1.name,
    COUNT(CASE WHEN t2.id1 = t1.id THEN 1 END) + COUNT(CASE WHEN t2.id2 = t1.id THEN 1 END) AS num
FROM customers t1
LEFT JOIN shifts t2 ON t1.id IN (t2.id1, t2.id2)
GROUP BY t1.id, t1.name

思路：LEFT JOIN保证所有客户都被包含，CASE语句仅在匹配时计数，最终相加得到总次数。

方法二：将两列拆分为行后统计 #

先把shifts表的id1和id2列拆成单独的行，再关联customers表统计次数：

SELECT 
    c.name,
    COUNT(s.customer_id) AS num
FROM customers c
LEFT JOIN (
    SELECT id1 AS customer_id FROM shifts WHERE id1 IS NOT NULL
    UNION ALL
    SELECT id2 AS customer_id FROM shifts WHERE id2 IS NOT NULL
) s ON c.id = s.customer_id
GROUP BY c.id, c.name

思路：用UNION ALL把id1和id2的有效数据合并成一列，这样统计次数就变成常规的分组计数，LEFT JOIN确保未出现的客户次数为0。

方法三：分别统计两列次数再合并 #

分别统计id1和id2中各客户的出现次数，再通过LEFT JOIN关联到customers表，用COALESCE处理NULL值后求和：

SELECT 
    c.name,
    COALESCE(q1.count1, 0) + COALESCE(q2.count2, 0) AS num
FROM customers c
LEFT JOIN (
    SELECT id1, COUNT(*) AS count1
    FROM shifts
    WHERE id1 IS NOT NULL
    GROUP BY id1
) q1 ON c.id = q1.id1
LEFT JOIN (
    SELECT id2, COUNT(*) AS count2
    FROM shifts
    WHERE id2 IS NOT NULL
    GROUP BY id2
) q2 ON c.id = q2.id2

思路：两个子查询分别统计id1和id2的次数，LEFT JOIN确保所有客户都被关联，COALESCE把未出现的NULL转为0，最终相加得到总次数。

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