MySql批处理的小窍门:排行榜类数据生成
最近在做新版本的开发,其中涉及到排行榜的批量预生成,在此分享给大家。
关键点
名次的计算(不考虑用游标)
单榜单查询
对于排行榜这种类型的数据,当只查一个排行榜时,由于数据量较少,我们可以直接查询后,在程序中生成名次(例如实时的只针对一个目的地下某类poi的好评榜)处理的时候php直接执行查询语句获取即可。
多榜单生成
但随着要生成的榜单数量增多,这种通过程序中转一次的方式,显然并不能令人满意,MySql中有没有一种方式,能够满足批量生成符合条件的排行榜时的名次批量生成吗?(例如:按特定要求生成排行榜,每个目的地下是否有排行榜,排行榜数量都不确定的情况)
如果能够生成名次列,我们就可以方便的通过INNER JOIN的方式将榜单成员记录和榜单的对应关系连起来。(这里就不赘述)
例子:多榜单的名次列生成
下面是一个批量生成分类型的排行榜(按评分score从高到低排序)
-- POI基础表CREATE TABLE temp_poi( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, -- 名称 type_id VARCHAR(255) NOT NULL, -- 类型 score int NOT NULL, -- 评分 PRIMARY KEY(id));-- 生成测试数据INSERT INTO temp_poi(name,type_id,score)VALUES('a',1,75),('b',2,28),('c',1,77),('d',3,55),('e',2,88),('f',3,37),('g',1,49),('h',2,57),('i',1,63),('j',3,44);-- 生成带名次的榜单SELECT type_id ,id ,score ,@position := if(@previous = type_id, @position, 0) + 1 AS position ,@previous := type_idFROM temp_poi , (SELECT @previous := -1, @position := 0) AS s -- 非常重要的一行ORDER BY type_id ,score DESC;
特别注意
其中一行代码:
, (SELECT @previous := -1, @position := 0) AS s
如果缺失会导致非常隐蔽的错误:在首次连接MySql后的第一次运行时会导致排名计算出错(之后就正常了)