数据库:
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
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产生出来有效很多。提示:SQL 可以使用子查询来进行实现。
--SQL 2005
Select id,Name,Grade,Dense_Rank() Over(Order By Grade Desc) As Rank
From dbo.TableName
Order By id
select t.* , (select count(1) from tb where grade >= t.grade) + 1 rank from tb t
insert into tb values(1 , 2 , 80 )
insert into tb values(2 , 2 , 90 )
insert into tb values(3 , 3 , 80 )
insert into tb values(4 , 4 , 88 )
goselect t.* , (select count(1) from tb where grade > t.grade) + 1 rank from tb tdrop table tb /*
id name grade rank
----------- ----------- ----------- -----------
1 2 80 3
2 2 90 1
3 3 80 3
4 4 88 2(所影响的行数为 4 行)
*/
再来一句,这是用count来模拟排名.Select id,Name,Grade,
(Select Count(Distinct ID) From TableName as t2 where t2.grade>t1.Grade)+1 As Rank
From TableName As t1
select
id,
name,
grade,
(select count(1) from tb where grade>=(select grade from tb where id=a.id order by grade desc limit 1)) as rank
from tb a/**
id name grade rank
----------- ----------- ----------- -----------
1 2 80 4
2 2 90 1
3 3 80 4
4 4 88 2
**/
id,
name,
grade,
(select count(1) from tb where grade>(select grade from tb where id=a.id order by grade desc limit 1))+1 as rank
from tb a
/**
id name grade rank
----------- ----------- ----------- -----------
1 2 80 3
2 2 90 1
3 3 80 3
4 4 88 2
**/
group by a.id,a.name,a.grade