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
已解决
https://www.cnblogs.com/kerrycode/p/5935704.html
这篇帖子对join的各种用法讲解比较清晰，可以参考一下
直接把目前的结果作为视图 查询
select t.* , (男的数量+女的数量) as 和 from ( 你现在查出来的结果) t
如何避免重复？？？？？？？？？？关键是这么转化后如何避免误差
GROUP BY 后的条件不对
因为你用V.ID分组了，每一个组里都有几个男人或女人，把组里的男人数相加，女人数相加才是总人数
其实你不用改，group by后边用sex分组，就对了
而且group by sex.只会显示小区里有家庭/人数/男女数据，如果这个小区没有人，那结果就不显示了，需要的结果是都显示
把你这里面涉及到的几张表的结构贴出来看看
根据前面的信息，推测你的几张表是相互关联的，看看下面的语句吧，应该可以正确获得你想要的结果
select v.name as 小区名称,ifnull(f.cnt,0) as 家庭数,ifnull(p.cnt,0) as 人员数,ifnull(pa.cnt,0) as 男人数,ifnull(ps.cnt,0) as 女人数,ifnull(c.cnt,0) as 汽车数
from village as v
left join (select villageID,count(1) cnt from family goup by villageID) as f on v.ID=f.villageID
left join (select xx1.villageID,count(1) cnt from
(select aa.ID as villageID,bb.ID as familyID,cc.id as personID,cc.sex
from village as aa
left join family as bb on aa.id=bb.villageID
left join personnel cc on cc.familyID=bb.id ) as xx1
group by xx1.villageID) as p on p.villageID=v.ID
left join (select xx2.villageID,count(1) cnt from
(select aa.ID as villageID,bb.ID as familyID,cc.id as personID,cc.sex
from village as aa
left join family as bb on aa.id=bb.villageID
left join personnel cc on cc.familyID=bb.id and cc.sex='男' ) as xx2
group by xx2.villageID) as pa on pa.villageID=v.ID
left join (select xx3.villageID,count(1) cnt from
(select aa.ID as villageID,bb.ID as familyID,cc.id as personID,cc.sex
from village as aa
left join family as bb on aa.id=bb.villageID
left join personnel cc on cc.familyID=bb.id and cc.sex='女' ) as xx3
group by xx1.villageID) as ps on ps.villageID=v.ID
left join (select xx4.villageID,count(1) cnt from
(select aa.ID as villageID,bb.familyID
from village as aa
left join family as bb on aa.id=bb.villageID
left join car cc on bb.id=cc.familyid) as xx4
group by xx4.villageID) as c on c.villageID=v.ID