一对多 group by 后查询 count(*) ，但是 count(*)=0的不能显示

本文链接：https://blog.youkuaiyun.com/Null_hc/article/details/72385493

博客内容涉及到在SQL查询中，如何正确获取一对多关系下，group by后的count(*)=0的记录。原始查询无法显示count(pt.id)=0的ts记录，通过修改查询条件并使用case when语句进行优化，可以确保所有满足条件的记录都能被查出，包括那些没有匹配子记录的情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查询在ts下面的pt已经完成的个数，但是按照下面的sql查询不出来 count(pt.id已完成)=0的ts记录
SELECT
ts.*,
COUNT(pt.id)
FROM
trial_record ts
LEFT JOIN trial_record tg ON ts.id = tg.p_id AND ts.trial_id = tg.trial_id
LEFT JOIN trial_record tc ON tc.p_id = tg.id AND tc.trial_id = tg.trial_id
LEFT JOIN ptrial_record pt ON pt.trial_id = tc.trial_id AND pt.p_id = tc.id
WHERE
pt.finished_exams >= pt.total_exams AND
tc.type = 4
AND tg.type = 3
AND ts.type = 2
and ts.trial_id in(
SELECT trial_id from trial_parent_middle WHERE trial_parent_id=202)
and pt.type=2
GROUP BY ts.id
ORDER BY t_id asc;
经考虑，改为：

SELECT
ts.*,
sum(case when pt.finished_exams>=pt.total_exams then 1 else 0 end) as counts
FROM
trial_record ts
LEFT JOIN trial_record tg ON ts.id = tg.p_id AND ts.trial_id = tg.trial_id
LEFT JOIN trial_record tc ON tc.p_id = tg.id AND tc.trial_id = tg.trial_id
LEFT JOIN ptrial_record pt ON pt.trial_id = tc.trial_id AND pt.p_id = tc.id
WHERE
tc.type = 4
AND tg.type = 3
AND ts.type = 2
and ts.trial_id in(
SELECT trial_id from trial_parent_middle WHERE trial_parent_id=202)
and pt.type=2
GROUP BY ts.id

可以查出全部结果，这个必须保证 where后边的条件都满足，若是tg和tc不存在也查不出来。