Access中实现排名和行号
2020-02-02 本文已影响0人
五维思考
很多朋友都碰到这个问题，如果在查询中生成一个行号。比如表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
