有三张表小区表,小区出售房源表,小区出租房源表,想查询出小区列表并获取到小区出售房源总数和出租房源总数, 怎么查询?
威尼斯人线上娱乐官网
威尼斯人线上娱乐官网
当前位置 : 威尼斯人线上娱乐官网 > 澳门威尼斯人娱乐城

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

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

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;

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

广告位

威尼斯人线上娱乐官网