MSSQL语句实现排名次

use pubs

go

 

select *,(select count(distinct min_lvl) from jobs a where a.min_lvl >= b.min_lvl ) mingci from jobs b order by min_lvl desc

 

结果:

/*

job_id job_desc                                           min_lvl max_lvl mingci     
------ -------------------------------------------------- ------- ------- -----------
2      Chief Executive Officer                            200     250     1
3      Business Operations Manager                        175     225     2
4      Chief Financial Officier                           175     250     2
5      Publisher                                          150     250     3
6      Managing Editor                                    140     225     4
7      Marketing Manager                                  120     200     5
8      Public Relations Manager                           100     175     6
9      Acquisitions Manager                               75      175     7
10     Productions Manager                                75      165     7
11     Operations Manager                                 75      150     7
12     Editor                                             25      100     8
13     Sales Representative                               25      100     8
14     Designer                                           25      100     8
1      New Hire - Job not specified                       10      10      9

*/

 

select *,排名=( select count(*) from ( select min_lvl from jobs) as a where min_lvl>b.min_lvl ) +1
from ( select *  from jobs ) as b order by 排名

 

 

select *,(select count(min_lvl) from (select min_lvl from jobs) a where a.min_lvl>jobs.min_lvl)+1 as mincis from jobs order by mincis asc  --不连续排名

 

select *,(select count(distinct min_lvl) from (select min_lvl from jobs) a where a.min_lvl>jobs.min_lvl)+1 as mincis from jobs order by mincis asc --连续排名

 

/*

job_id job_desc                                           min_lvl max_lvl 排名         
------ -------------------------------------------------- ------- ------- -----------
2      Chief Executive Officer                            200     250     1
3      Business Operations Manager                        175     225     2
4      Chief Financial Officier                           175     250     2
5      Publisher                                          150     250     4
6      Managing Editor                                    140     225     5
7      Marketing Manager                                  120     200     6
8      Public Relations Manager                           100     175     7
9      Acquisitions Manager                               75      175     8
10     Productions Manager                                75      165     8
11     Operations Manager                                 75      150     8
12     Editor                                             25      100     11
13     Sales Representative                               25      100     11
14     Designer                                           25      100     11
1      New Hire - Job not specified                       10      10      14

*/

create   table   tb(考号   varchar(10),科目代号   varchar(10),得分   int)  
  insert   into   tb   select   '0001'   ,'01',   60  
  union   all   select   '0001'   ,'02',   80  
  union   all   select   '0001'   ,'03',   90  
  union   all   select   '0001'   ,'04',   100  
  union   all   select   '0002'   ,'01',   50  
  union   all   select   '0002'   ,'02',   40  
  union   all   select   '0002'   ,'03',   60  
  union   all   select   '0002'   ,'04',   90  
   
  declare   @sql   varchar(8000)  
  set   @sql='select   a.考号'  
  select   @sql=@sql+',[科目'+cast(科目代号   as   varchar)+']=sum(case   a.科目代号   when   '''+cast(科目代号   as   varchar)+'''   then   a.得分   else   0   end)'   from   tb   group   by   科目代号  
  exec(@sql+',sum(a.得分)   as   总分,identity(int,1,1)   as   名次   into   tmp   from   tb   a   group   by   a.考号   order   by   sum(a.得分)   desc')  
  select   *   from   tmp  
   
  drop   table   tb,tmp