有三张表小区表，小区出售房源表，小区出租房源表，想查询出小区列表并获取到小区出售房源总数和出租房源总数， 怎么查询？

Question

有小区表community,字段id,name;小区出售房源表sale,字段id,name,cid;小区出租房源表rent,字段id,name,cid;cid与小区字段id关联，想查询到小区列表并获取每个小区的出售房源总数和出租房源总数，最终结果的样子大概是id,name,sale_count,rent_count，如何去查询？

Answer 1

select 
C.id,
C.name,
(
    select count(*) from sale as S where S.cid = C.id
) as sale_count , 
(
    select count(*) from rent as R where R.cid = C.id
) as rent_count 
from community as C;

Answer 2

SELECT community.id,community.name,S_COUNT.sale_count,R_COUNT.rent_count FROM community LEFT JOIN (SELECT sale.cid,count(sale.id) as sale_count from sale GROUP BY sale.cid) AS S_COUNT ON community.id=S_COUNT.cid LEFT JOIN (SELECT rent.cid,count(rent.id) as rent_count from rent GROUP BY rent.cid) AS R_COUNT ON community.id=R_COUNT.cid