select STATIONID,row_number() over (partition by substr(stationid,1,4) order by stationid) ID from stationsinfo 根据substr(stationid,1,4)分组,根据stationid排序,列出子序号
结果:
stationid | id |
G020L002140108 | 1 |
G020L004141102 | 2 |
G020L005141102 | 3 |
G025L102130731 | 1 |
G030L008620981 | 1 |
G030L010620922 | 2 |
G030L120620121 | 3 |
G035L117140321 | 1 |
G035L119140110 | 2 |
G040L128140781 | 1 |
G040L131141082 | 2 |
G040L136140882 | 3 |
G045L001650106 | 1 |
G045L001652301 | 2 |
G045L002650200 | 3 |