MySQL如何查询含无子合同的父合同及对应子合同数量

问题根因 #

你现有SQL的左连接表顺序写反了：把子合同作为左表关联父合同，最终查询结果只会保留至少关联了1个子合同的父合同，没有子合同的父合同（包括你业务中定义的parent_id IS NULL的顶层父合同）都会被过滤，自然查不到orders=0的父合同数据。
另外现有SQL还存在两个语法/逻辑瑕疵：

• sup.contractor_name字段后缺少逗号，直接执行会报语法错误
• 子合同的删除过滤条件如果写在WHERE里，会把没有子合同的父合同一并过滤

修正后SQL #

SELECT  
    sup.id_contract, 
    sup.parent_id, 
    sup.contractor_name,
    IFNULL(COUNT(sub.id_contract), 0) AS orders
FROM Contracts sup
LEFT JOIN Contracts sub 
    ON sub.parent_id = sup.id_contract 
    AND sub.deleted != 1
WHERE sup.deleted != 1 
  AND sup.parent_id IS NULL -- 符合你业务中父合同的判定规则，如有其他父合同判定条件可在此补充
GROUP BY sup.id_contract, sup.parent_id, sup.contractor_name;

调整点说明 #

• 交换左连接的表顺序，把父合同作为左表，保证所有未删除的父合同都会被保留，不管有没有对应的子合同
• 子合同的deleted !=1过滤条件放在LEFT JOIN的ON子句中，避免过滤掉没有子合同的父合同
• 补全GROUP BY的所有非聚合字段，符合标准SQL规范，避免MySQL非严格模式下的非法分组异常
• 补充sup.parent_id IS NULL条件限定父合同范围，匹配你的业务规则

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