使用GROUP BY与IN运算符时如何返回0计数的结果集

要让Query1在指定的maintenance_request_id没有匹配记录时返回row_count=0，核心思路是保留所有要查询的ID作为基础数据集，通过左连接关联原表数据，再处理空计数。以下是具体实现：

方法1：手动指定目标ID（适合临时查询） #

将需要查询的maintenance_request_id用VALUES生成临时表，作为左连接的左表，确保每个ID都会出现在结果中：

SELECT  ids.maintenance_request_id,
        COALESCE(count(fa.id), 0) AS row_count 
FROM (
    -- 这里可以添加多个ID，比如(17312470), (12345678), (98765432)
    VALUES (17312470)
) AS ids(maintenance_request_id)
-- 左连接file_associations，将原WHERE条件移至ON子句（避免过滤左表行）
LEFT JOIN file_associations fa 
    ON fa.maintenance_request_id = ids.maintenance_request_id
    AND fa.cid = 12345 
    AND fa.deleted_by IS NULL 
    AND fa.deleted_on IS NULL
-- 左连接files表，保持原关联逻辑
LEFT JOIN files f 
    ON f.id = fa.file_id AND f.cid = fa.cid
GROUP BY ids.maintenance_request_id

方法2：从维护请求表获取目标ID（适合有对应业务表的场景） #

如果存在存储维护请求的表（比如maintenance_requests），可以直接从该表筛选目标ID，再左连接关联数据：

SELECT  mr.id AS maintenance_request_id,
        COALESCE(count(fa.id), 0) AS row_count 
FROM maintenance_requests mr
LEFT JOIN file_associations fa 
    ON fa.maintenance_request_id = mr.id
    AND fa.cid = 12345 
    AND fa.deleted_by IS NULL 
    AND fa.deleted_on IS NULL
LEFT JOIN files f 
    ON f.id = fa.file_id AND f.cid = fa.cid
-- 筛选需要查询的ID
WHERE mr.id IN (17312470)
GROUP BY mr.id

关键说明： #

1. 左连接的作用：左连接会保留左表（目标ID集合）的所有行，即使右表（原业务表）没有匹配数据，这样每个目标ID都会出现在结果中。
2. 条件移至ON子句：原Query1的WHERE条件如果放在左连接后的WHERE中，会过滤掉右表无匹配的行，因此必须将业务过滤条件移至JOIN的ON子句中。
3. COALESCE处理空计数：当右表无匹配时，count(fa.id)会返回NULL，用COALESCE将其转为0，得到预期的计数结果。

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