写点什么

MYSQL 分组排名

  • 2021 年 11 月 11 日
  • 本文字数:1895 字

    阅读完需:约 6 分钟

今天遇到一个 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:=SHHNAME


from


(


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:='') t2


order by SHHNAME,evtdate desc,stkabb ) t


修改后


select t.*


, o.stkabb


from


(


select t1.*


,@rank


【一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义】
浏览器打开:qq.cn.hn/FTf 免费领取
复制代码


:= (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.COMUNIC


where t.rank<=10


order by shhname,rank


最后附上本人修改的代码


SELECT


*, (


SELECT


rank


FROM


(


SELECT


s1id,


rname,


@rank := (


CASE


WHEN @Grp = rid THEN


@rank + 1


ELSE


1


END


) AS rank,


@Grp := rid AS grp2


FROM


(


SELECT



    FROM


    v_wx_mini_shop_user_info


    ) tmp,


    (SELECT @rank := 0 ,@grp := 0) a


    ORDER BY


    rid DESC


    ) RESULT


    WHERE


    s1id = ssa.s1id


    ) AS crank,


    (

    评论

    发布
    暂无评论
    MYSQL 分组排名