針對5.7.3的版本,文章一定要看"注意點"部分
一、分組排序
實現(xiàn)如下功能：row_number()over(partition by A order by B)
1、場景描述：每門課程按照分數(shù)升序排序缨称。
SELECT
a.sid ,
a.score,
-- a.cid , 不能有該字段睦焕，添加該字段，不能分組排序穿香，是全局排序
( @rank := CASE WHEN @cid = a.cid THEN @rank + 1 ELSE 1 END ) AS row_num,
( @cid := a.cid ) AS cid2
-- 字段中必須有字段邏輯 ( @cid := a.cid )茧吊，否則不能實現(xiàn)分組排序贞岭；且分組字段必須在排序字段后面，否則是針對所有數(shù)據(jù)排序
FROM
(select
*
from
(
-- sid ：學升搓侄；cid ：課程編碼瞄桨； score ：成績
select '小明' sid,'cid-01' cid, 90 score union all
select '小明' sid,'cid-02' cid, 67 score union all
select '小明' sid,'cid-03' cid, 88 score union all
select '張三' sid,'cid-01' cid, 78 score union all
select '張三' sid,'cid-02' cid, 66 score union all
select '張三' sid,'cid-03' cid, 55 score union all
select '李四' sid,'cid-01' cid, 99 score union all
select '李四' sid,'cid-02' cid, 94 score union all
select '李四' sid,'cid-03' cid, 90 score
)t
ORDER BY cid, sid DESC
) as a
INNER JOIN ( SELECT @rank := 0, @cid := 0 ) AS b ON 1 =1
注意(重點！Ｑ茸佟芯侥！!)：
select中必須有字段邏輯( @cid := a.cid )，否則不能實現(xiàn)分組排序；且分組字段a.cid 柱查、@cid := a.cid必須在排序字段后面廓俭，否則是針對所有數(shù)據(jù)排序
二、全局排序
實現(xiàn)如下功能：row_number()over(order by B)
1唉工、場景描述：按照月份倒序
select
date_month
,months
,@rank:= @rank+1 rn -- 排序
from
(select
t.*
from
(select '2022-08' date_month,'8月' months union all
select '2022-09' date_month,'9月' months union all
select '2022-03' date_month,'3月' months union all
select '2022-04' date_month,'4月' months union all
select '2022-05' date_month,'5月' months union all
select '2022-06' date_month,'6月' months union all
select '2022-07' date_month,'7月' months union all
select '2022-01' date_month,'1月' months union all
select '2022-02' date_month,'2月' months
)t
order by date_month desc)a,( SELECT @rank := 0, @cid := 0 ) b
