SQL实现排序，并输出排序后的序号

问题遇到的现象和发生背景

一张表有ABCDE 这5列，B列和C列是D列和E列的排序序号，A列是固定不动的

我的解答思路和尝试过的方法

网上找的两种方式都不对
select enter_station
ROW_NUMBER() over (order by enter_station desc) as enter_station_no
from TBL_PASSENGER_FLOW_STATISTICS

set @rank=0;
select station,enter_station,leave_station,enter_station_no,leave_station_no,@rank :=@rank+1 enter_station_no
from TBL_PASSENGER_FLOW_STATISTICS

我想要达到的结果

就像这个图进站量和出站量名次那样 D列和E列分别是B列和C列排序的序号，不是按顺序排的，求指点啊

窗口函数ROW_NUMBER()就能实现你的需求了，从一个维度排序，排序结果从1开始没毛病，

with t1 as (
SELECT '西桥站' NAME ,'1847' ID,'1536' ID2 UNION ALL
SELECT '化章西站街站' NAME ,'0' ID,'0' ID2 UNION ALL
SELECT '晋中环站' NAME ,'5846' ID,'6781' ID2 UNION ALL
SELECT '长风街站' NAME ,'6224' ID,'7426' ID2 UNION ALL
SELECT '嘉节站' NAME ,'1719' ID,'1222' ID2 )

select name
,id
,id2
,row_number()over(order by id desc ) id3
,row_number()over(order by id2 desc ) id4
from t1
order by case NAME when '西桥站' then 1
when '化章西站街站' then 2
when '晋中环站' then 3
when '长风街站' then 4
when '嘉节站' then 5
end asc