select 学号,日期,row_number() over(partition by 学号 order by 学号) from A表;
Mysql利用用户变量rowno获取排序后的序号,相当于Oracle的伪列
select id,score,(@rowno:=@rowno+1) as rowno from test,(select (@rowno:=0)) b order by score desc;
注:@rowno:=0,用@定义,表示rowno变量的初始值为0。
--组内排序
SELECT id,te FROM test ORDER BY te DESC,id DESC;
--组内排序加自动列(序号-相当于表的行号,从1开始)
SELECT id,te,(@rowno:=@rowno+1) AS rowno FROM test,(SELECT (@rowno:=0)) b ORDER BY te DESC,id DESC;
--组内排序加自动列(序号-每组从1开始)
SELECT id,te,rank FROM
(SELECT t.id,t.te,@rownum:=@rownum+1,IF(@i=t.te,@rank:=@rank+1,@rank:=1) AS rank,@i:=t.te FROM
(SELECT id,te FROM test ORDER BY te ASC,id DESC) t ,(SELECT @rownum :=0 , @i := NULL ,@rank:=0) a
) result ;
如何在sql排序后给排好的数据加序号
最新推荐文章于 2024-05-06 15:25:56 发布