MSSQL语句实现排名次

select *,(select count(distinct min_lvl) from (select min_lvl from jobs) as b where b.min_lvl>jobs.min_lvl)+1 as mici from jobs order by mici

 

 

结果:

/*

job_id job_desc                                           min_lvl max_lvl mici       
------ -------------------------------------------------- ------- ------- -----------
114    CKCK                                               56      67      1
103    Accountant                                         12      125     2
106    Accountant                                         12      125     2
107    Accountant                                         12      125     2
108    Accountant                                         12      125     2
109    Accountant                                         12      125     2
110    Accountant                                         12      125     2
111    Accountant                                         12      125     2
112    Accountant                                         12      125     2
6      Managing Editor                                    7       225     3
5      chen jian ping                                     5       250     4
4                                                         4       250     5
101    cjc[                                               3       3       6
102    cjc [                                              3       3       6
9      中华人民共和国                                            0       67      7

 

*/

 

 

select *,(select count(min_lvl) from (select min_lvl from jobs) as b where b.min_lvl>jobs.min_lvl)+1 as mici from jobs order by mici

结果:

/*

job_id job_desc                                           min_lvl max_lvl mici       
------ -------------------------------------------------- ------- ------- -----------
114    CKCK                                               56      67      1
103    Accountant                                         12      125     2
106    Accountant                                         12      125     2
107    Accountant                                         12      125     2
108    Accountant                                         12      125     2
109    Accountant                                         12      125     2
110    Accountant                                         12      125     2
111    Accountant                                         12      125     2
112    Accountant                                         12      125     2
6      Managing Editor                                    7       225     10
5      chen jian ping                                     5       250     11
4                                                         4       250     12
101    cjc[                                               3       3       13
102    cjc [                                              3       3       13
9      中华人民共和国                                            0       67      15

 

*/