id name grade
1 2 80
2 2 90
3 3 80
4 4 88
一条Select语句打印如下信息：
id name grade rank(排名)
1 2 80 3
2 2 90 1
3 3 80 3
4 4 88 2
------解决方案--------------------
- SQL code
--sql2005select *,DENSE_RANK() over( order by grade) from tb--sql 2000select *,排名=(select count(distict grade) from tb where grade>=a.grade)from tb a
------解决方案--------------------
- SQL code
declare @t table (id int,name int,grade int)insert into @tselect 1,2,80 union allselect 2,2,90 union allselect 3,3,80 union allselect 4,4,88select *,dense_rank() over (order by grade desc) as rank from @t/*id name grade rank----------- ----------- ----------- --------------------2 2 90 14 4 88 21 2 80 33 3 80 3*/
------解决方案--------------------
- SQL code
create table tb (id int,name int,grade int) insert into tb select 1,2,80 union select 2,2,90 union select 3,3,80 union select 4,4,88 --sql2005select *,DENSE_RANK() over( order by grade desc) from tb--sql 2000select *,排名=(select count(distinct grade) from tb where grade>=a.grade)from tb aid name grade ----------- ----------- ----------- --------------------2 2 90 14 4 88 21 2 80 33 3 80 3(4 行受影响)id name grade 排名----------- ----------- ----------- -----------1 2 80 32 2 90 13 3 80 34 4 88 2(4 行受影响)