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

时间:2021-06-09 14:20:29

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表示电影唯一编号, 一个电影可以在多个城市上映,唯号同)

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

因为若以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

执行结果为:

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

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

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