高手请指点一下,看看这句SQL 语句怎么写合适小区表 Community
字段:CommunityId 小区Id,主键;
CommunityName 小区名称;
小区楼号表 Building
字段: BuildingId 楼号id,主键
BuildingNo 楼号
CommunityId 小区id ,小区表外键
房间号表 house
字段: HouseId 房间Id,主键
userId 入住用户Id,用户表外键
buildingId 楼号Id,小区楼号表的外键.
查出入住最多的前四个小区和这前四个小区的入住的人数。
字段:CommunityId 小区Id,主键;
CommunityName 小区名称;
小区楼号表 Building
字段: BuildingId 楼号id,主键
BuildingNo 楼号
CommunityId 小区id ,小区表外键
房间号表 house
字段: HouseId 房间Id,主键
userId 入住用户Id,用户表外键
buildingId 楼号Id,小区楼号表的外键.
查出入住最多的前四个小区和这前四个小区的入住的人数。
top 4
a.CommunityName,
count(*) as 人數
from Community a
inner join Building b on a.CommunityId=b.CommunityId
inner join house c on c.buildingId=b.ID
group by a.CommunityName
order by count(*) desc
join building AS b ON a.communityid = b.communityid
JOIN house AS c ON c.buildingid = b.buildingid
GROUP BY a.communityname
ORDER BY COUNT(c.buildingid) DESC
[小区] = CTY.CommunityName ,
[人数] = COUNT(H.UserId)
FROM community CTY
LEFT JOIN Building BG ON CTY.CommunityID = BG.CommunityID
LEFT JOIN House H ON BG.BuildingID = H.BuildingID
GROUP BY CTY.CommunityName
ORDER BY COUNT(H.UserId) DESC