sql 怎么用户查询积分排名

[accp1]

name integral
aa 100
bb 200
cc 150
dd 180


select * from jf where name ='cc'

现在我要得到cc这个用户的排名3这个数字

[Mr_Nice]

按照什么来排？

数字，还是名字？

select nn
from (
select row_number() over(order by getdate()) as nn,name,integral from TB)T
where name ='CC'

???

[jyh070207]

create table jf(name varchar(20),integral int)
insert into jf(name,integral)
select 'aa',100
union 
select 'bb',200
union
select 'cc',150
union
select 'dd',180


SELECT *,Place=(SELECT COUNT(DISTINCT integral) FROM jf WHERE integral>=a.integral)
FROM jf a
ORDER BY Place

/*
name   integral  place
bb	200	1
dd	180	2
cc	150	3
aa	100	4
*/

[开着拖拉机泡妞]

seelct rankcc from(
select rankcc=row_number()over(order by getdate()),* from tbl
)a where name='cc'

[十三门徒]

create table jf
(name varchar(10),
integral integer
)
insert into jf(name,integral)
select 'aa',100
union 
select 'bb',200
union
select 'cc',150
union
select 'dd',180
select *,rank() over (order by integral ) as ordernum from jf where name='cc'

[十三门徒]

更正上面语句

select * from (select *,rank() over (order by integral desc) as ordernum from jf)c where name='cc'

[accp1]

我用的是sql server2000 rank() 、row_number() 不知道这样的函数
以前的老服务器

[tommore]

select ( select count(*)
            from   tb_product p1
            where  p1.proid <= p2.proid)  as rowNumber,
* from tb_product p2
order by rowNumber

[tommore]

select ( select count(*)
            from   jf p1
            where  p1.name <= p2.name)  as rowNumber,
* from jf p2
order by rowNumber

[孤独加百列]

CREATE TABLE jf
(
	name VARCHAR(10),
	integral int
)

GO

INSERT INTO jf
SELECT 'aa', 100 UNION
SELECT  'bb', 200 UNION
SELECT  'cc', 150 UNION
 SELECT 'dd', 180
 
 
 SELECT a.name,a.integral,COUNT(c.name) AS rank
 FROM jf as a,jf AS c
 WHERE a.name = 'cc' AND c.integral >= a.integral
 GROUP BY a.name,a.integral