mysql 查询每个分组前N条记录

mysql 查询每个分组前N条记录

假设存在表movie,  有字段 id, part（地区）， mcount（观看次数）

现查询每个地区观看次数最多的3部movie, 则表

###id虽未存在group列表 但不报错，原因未知…

select a.part,a.id, a.mcount from movie a, movie b

#下面的where子句产生迪卡尔积并进行筛选， 最大记录与自己产生一条记录（以自身数据为key）， 第二位的 将与最大的数据及自身产生两条数据 ……以次类推
where a.part = b.part and a.mcount <= b.mcount 
group by a.part, a.mcount

#选出以（part, mcount）为key的分组中不大于3的分组， 即为所求
having count(a.part) <= 3

 

由于之前，未完全理解数据含义，并未覆盖所有情况，做出如上结果sql，待朋友给出特殊数据后，才知存在问题。

上面这条语句不能处理有重复的记录如 天津有两个mcount为3但id不同的记录（id表示电影唯一编号， 一个电影可以在多个城市上映，唯号同）

因为若以part, 和mcount为选择条件时  id为1  part天津 mcount 3  与  id为4 part天津 mcount 3的记录 产生的积也符合条件 ，其为key的分组将大于3 被过滤过

 

可用如下修正过的SQL：

select a.id, a.part, a.mcount from movie a , movie b

#####若mcount不等时产生的积为合法， mcount相等时 判断是否为自身产生，若是,合法;若不是，不合法#####
where a.part = b.part
    and ((a.mcount = b.mcount and a.id = b.id) or (a.mcount < b.mcount))　　　
group by a.part, a.mcount, a.id　　　###三个字段为key进行分组
having count(*) <= 3
order by part, mcount desc
 

执行结果为：

 

坏处： 自身进行迪卡尔积，若数据量大， 产生中间临时数据将过大

 http://www.cnblogs.com/barrenlake/p/4399245.html