SQL分组获取第6到10条数据问题

[空白桑]

现有3张表
表A 酒店表
ID AName ACode
表B 房间表
ID AID BNum BPrice
表C 收款表
ID BID CName CAccount
表A与表B是一对多 一个酒店多应多个房间 一个房间只属于一个酒店
表B与表C是一对多 一个房间对应多笔收款 一笔收款只属于一个房间

现在要查
每个酒店房间价格（BPrice）在该酒店降序排序为第6到第10的房间的酒店名称、房间号（BNum ）、累计收款金额（CAccount）

看着有点没头绪
主要是要查询每个酒店 房间价格在第6到10的房间信息 ，，， 这点怎么弄
求指点~~~

[RINK_1]

SELECT AName AS 酒店名称, BNum AS 房间号, TOTAL AS 累计收款金额 FROM ( SELECT ANAME, BNUM, B.ID, ROW_NUMBER() OVER(PARTITION BY A.ID ORDER BY BPRICE DESC) AS RN FROM 酒店表 A LEFT JOIN 房间表 B ON A.ID=B.AID ) AS A JOIN (SELECT BID,SUM(CAccount) AS TOTAL FROM 收款表 GROUP BY BID) AS B ON A.ID=B.BID WHERE RN>=6 AND RN<=10

[空白桑]

引用 1 楼 RINK_1 的回复:

SELECT AName AS 酒店名称, BNum AS 房间号, TOTAL AS 累计收款金额 FROM ( SELECT ANAME, BNUM, B.ID, ROW_NUMBER() OVER(PARTITION BY A.ID ORDER BY BPRICE DESC) AS RN FROM 酒店表 A LEFT JOIN 房间表 B ON A.ID=B.AID ) AS A JOIN (SELECT BID,SUM(CAccount) AS TOTAL FROM 收款表 GROUP BY BID) AS B ON A.ID=B.BID WHERE RN>=6 AND RN<=10

厉害厉害厉害 我写了两年多SQl都没用过PARTITION