如何在sql排序後給排好的資料加序号

select 學号,日期,row_number() over(partition by 學号 order by  學号) from A表;
Mysql利用使用者變量rowno擷取排序後的序号，相當于Oracle的僞列
select id,score,(@rowno:[email protected]+) as rowno from test,(select (@rowno:=)) 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:[email protected]+) AS rowno FROM test,(SELECT (@rowno:=)) b ORDER BY te DESC,id DESC;

--組内排序加自動列（序号-每組從1開始）
SELECT id,te,rank FROM
(SELECT t.id,t.te,@rownum:[email protected]+,IF(@i=t.te,@rank:[email protected]+,@rank:=) AS rank,@i:=t.te FROM
(SELECT id,te FROM test ORDER BY te ASC,id DESC) t ,(SELECT @rownum := , @i := NULL ,@rank:=) a
) result ;