数据表结构：
CREATE TABLE `d_activity_momoking` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` bigint(20) NOT NULL COMMENT '用户编号',
`faction` int(11) NOT NULL COMMENT '阵营：1.天宫，2.花果山',
`week` int(11) NOT NULL COMMENT '第几周',
`score` int(11) NOT NULL DEFAULT '0' COMMENT '积分',
`server` int(11) NOT NULL DEFAULT '0' COMMENT '服务区编号',
PRIMARY KEY (`id`),
UNIQUE KEY `sign` (`uid`,`week`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
理清思路： 大坑比的需求是要根据 week，server、faction 分组 ，再根据score 分数 倒序 取出 前三名！！
解决办法：
1、使用mysql 变量 实现排序字段
set @order_num=0; #定义一个排序字段
SELECT uid, week, score, @order_num:[email protected]_num+1 rownum FROM d_activity_momoking order by score desc limit 10; (无限制分数排序)
2、实现分组
需要分三个组 那我们先实现 week （时间）分组吧 就会让@week 等于 week字段 排序的时候根据week asc 排序
set @order_num=0;set @week='';
SELECT uid, week, score,case when @week = week then @order_num:[email protected]_num+1 else @order_num:=1 end rownum, @week:=week FROM d_activity_momoking order by week,score desc limit 15;
3、没有达到效果啊！ （ 最终 inner join 自己 查询）
set @order_num=0;set @week='';
select a.*,b.rownum from d_activity_momoking a inner join (
SELECT uid, week, score,case when @week = week then @order_num:[email protected]_num+1 else @order_num:=1 end rownum, @week:=week FROM d_activity_momoking order by week,score desc
) b on a.uid=b.uid and a.week=b.week where b.rownum <=5
;
实现了 week 时间分组！！！ 还有分组怎么办 继续完善sql !!
4.set @order_num=0;set @week='';set @server='';set @faction='';
select a.*,b.rownum from d_activity_momoking a inner join (
SELECT uid, week,score,faction,case when @week = week and @server=`server` and @faction=`faction` then @order_num:[email protected]_num+1 else @order_num:=1 end rownum,
@week:=week,@server:=`server`,@faction:=faction FROM d_activity_momoking order by week,`server`,faction,score desc
) b on b.uid=a.uid and b.week=a.week where b.rownum<=3;
#注意 其他关键字段 asc 升序 哦
各周 1服 3服 （数据中只有1，3服） 阵营1 和 2 的前三名就查询出来了！！！！
小懒懒： 大坑比 你过来 是不是 你要的效果！！！！！！
以上数据为测试数据 大约5W左右 87ms出数据！！（好快哦 ！！！）