Access中实现排名和行号

很多朋友都碰到这个问题，如果在查询中生成一个行号。比如表tblA

image.png

想按分数来得到名次，如何实现？ 下面教你在Access中使用SQL语句实现类似SQL Server中row_number()的效果。

一、按成绩排序，并列者向下顺延

注:如下没有第三名，并列两个第四

image.png

-- 标准SQL
select a.ID, a.CNAME, a.SCORE,count(*) as SNO
from tblA a inner join tblA b on a.SCORE<=b.SCORE
group by a.ID, a.CNAME, a.SCORE
order by a.SCORE desc 
-- Access环境
select id,CNAME,SCORE,DCOUNT('ID','tblA','SCORE>=' & SCORE) as SNO
from tblA
order by 4 

二、按成绩排序，并列者向上顺延

注:如下没有第四名，并列两个第三

image.png

-- 标准SQL
select a.ID, a.CNAME, a.SCORE,count(b.ID)+1 as SNO
from tblA a left join tblA b on a.SCORE<b.SCORE
group by a.ID, a.CNAME, a.SCORE
order by a.SCORE desc 
-- Access环境
select id,CNAME,SCORE,DCOUNT('ID','tblA','SCORE>' & SCORE)+1 as SNO
from tblA
order by 4 

三、按成绩排序，并列者以学号大者在先。

image.png

-- 标准SQL
select a.ID, a.CNAME, a.SCORE,count(*) as SNO
from tblA a inner join tblA b on (a.SCORE<b.SCORE or (a.SCORE=b.SCORE and a.ID<=b.ID))
group by a.ID, a.CNAME, a.SCORE
order by 4 
-- Access环境
select id,CNAME,SCORE,DCOUNT('ID','tblA','SCORE>' & SCORE & ' OR (SCORE=' & SCORE & ' AND ID>=' & ID & ')' ) as SNO
from tblA
order by 4