首页 > 代码库 > MYSQL 分组排名
MYSQL 分组排名
今天遇到一个MYSQL排序的问题,要求按某列进行分组,组内进行排序.
百度一下发现MYSQL不支持row_number(),rank()等函数.
采用的办法如下,我们首先创建一个测试表:
--创建表create table Rank_test(ID int,SCORE int,grp int)--插入数据insert into Rank_test values(1 , 28,1);insert into Rank_test values(2 , 33,1);insert into Rank_test values(3 , 33,1);insert into Rank_test values(4 , 89,1);insert into Rank_test values(5 , 99,1);insert into Rank_test values(6 , 68,1);insert into Rank_test values(7 , 68,1);insert into Rank_test values(8 , 78,1);insert into Rank_test values(9 , 88,1);insert into Rank_test values(10, 90 ,1);insert into Rank_test values(11, 28,2); insert into Rank_test values(12, 33,2); insert into Rank_test values(13, 33,2); insert into Rank_test values(14, 89,2); insert into Rank_test values(15, 99,2); insert into Rank_test values(16, 68,2); insert into Rank_test values(17, 68,2); insert into Rank_test values(18, 78,2); insert into Rank_test values(19, 88,2); insert into Rank_test values(20, 90 ,2);
分组排序的SQL代码:
SELECT id, score, rank FROM (SELECT tmp.id, tmp.score, @rank := (case when @Grp = grp then @rank + 1 else 1 end) AS rank , @Grp:=grp as grp2 FROM (SELECT id, score, grp FROM rank_test) tmp, (SELECT @rank := 0,@grp:=0) a order by grp,score desc) RESULT;
结果如下:
测试结果OK,但重点是套用到我们的SQL中后,排序结果完全不是我们预期中的,而且没有找到规律.
经过几次尝试,最后重新改写了SQL代码,终于成功了.
原因是套用下面这段代码时,我们是用了几张表进行关联(inner join ),最后将inner join 的表全部改为where 语句中的子句,结果满足我们的预期.
(SELECT id, score, grp FROM rank_test) tmp,
所以建议在这里的代码尽量使用单表,不要多张表关联
附上修改前后的代码:(红色部分是修改前,绿色部分是修改后)
select * from (select evtdate ,stkabb,chng,PCTCSHG,cshg,SHHNAME ,@rank := (case when @SHHNAME=SHHNAME then @rank + 1 else 1 end) AS rank ,@SHHNAME:=SHHNAMEfrom (select tt.SHHNAME -- 股东名称 ,s.evtdate -- 变动日期 ,r.stkabb -- 变动股票名称 ,ifnull(s.RLDSHG,s.cshg-s.SHGBCH)/10000 as chng -- 变动股数（万股） ,s.cshg/10000 as cshg-- 变动后持股（万股） ,s.PCTCSHG -- 变动后持股比例% from pgenius.hk_stkcode r inner join pgenius.HK_SAKCHMJSHH s on r.comunic = s.comunic inner join (select distinct b.SHHNAME from pgenius.hk_stkcode a inner join pgenius.HK_SAKCHMJSHH b on a.comunic = b.comunic and b.evtdate = (select max(evtdate) from pgenius.HK_SAKCHMJSHH where comunic = b.comunic and SHHNAME = b.SHHNAME and NTUREFCINTRTS = b.NTUREFCINTRTS) where a.lssturefc = 1 and b.cshg > 0 and a.stkcode = ‘00001‘ ) tt on s.SHHNAME = tt.SHHNAME) t1,(SELECT @rank := 0,@SHHNAME:=‘‘) t2order by SHHNAME,evtdate desc,stkabb ) t
select t.* , o.stkabb from ( select t1.* ,@rank := (case when @SHHNAME=SHHNAME then @rank + 1 else 1 end) AS rank ,@SHHNAME:=SHHNAME from ( select s.evtdate ,ifnull(s.RLDSHG,s.cshg-s.SHGBCH)/10000 as chng -- 变动股数（万股） ,s.cshg/10000 as cshg-- 变动后持股（万股） ,s.PCTCSHG -- 变动后持股比例% ,SHHNAME ,s.COMUNIC from pgenius.HK_SAKCHMJSHH s where SHHNAME in ( select distinct shhname from pgenius.HK_SAKCHMJSHH a where comunic=(select COMUNIC from pgenius.hk_stkcode where stkcode=‘00001‘) and ISVALID=1 and not exists(select 1 from pgenius.HK_SAKCHMJSHH where COMUNIC=a.COMUNIC and SHHNAME=a.SHHNAME and evtdate>a.evtdate) and cshg > 0) ) t1, (SELECT @rank := 0,@SHHNAME:=‘‘) t2 order by SHHNAME,evtdate desc ) t inner join pgenius.hk_stkcode o on t.COMUNIC= o.COMUNICwhere t.rank<=10order by shhname,rank
MYSQL 分组排名
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。