left join +group by；总结

Question

SELECT v.name,COUNT(DISTINCT f.ID)AS family,COUNT(DISTINCT p.ID)AS person ,COUNT(DISTINCT pa.ID) AS man,COUNT(DISTINCT ps.ID)AS woman ,COUNT(DISTINCT c.id)AS car
FROM village AS v
LEFT JOIN family AS f ON f.villageID=v.ID AND f.building='3'
LEFT JOIN personnel AS p ON p.familyid=f.ID AND f.building='3'
LEFT JOIN personnel AS pa ON pa.familyid=f.ID AND pa.sex='男' AND f.building='3'
LEFT JOIN personnel AS ps ON ps.familyid=f.ID AND ps.sex='女' AND f.building='3'
LEFT JOIN car AS c ON c.familyid=f.ID AND f.building='3'
where　 v.ID='d66cf963-2354-11e8-b8a0-f48e38f841f1'
GROUP BY f.building

已解决

Answer 1

https://www.cnblogs.com/kerrycode/p/5935704.html
这篇帖子对join的各种用法讲解比较清晰，可以参考一下