如何确定某条记录在表中排在第几位?
表A有两个字段id(自增),hit(int 型)
id hit
2 50
3 12
4 30
5 105
6 54
7 30
现在我如何知道 id=6 这条记录,在以hit字段为降序时它排在第几位?
[解决办法]
晕
select count(*)+1 from A
where hit> (select hit from A where id=6)
[解决办法]
Select Count(*) From A
Where hit > = (Select hit From A Where id = 6)
[解决办法]
SELECT IDENTITY(int, 1,1) AS seq ,* into #cc from Aorder by hit desc;
select seq from #cc where [id]=6;
[解决办法]
select count(*)+1 from A t
where exists(select 1 from A where hit <t.hit and id=6)
[解决办法]
create table #temp
(id int,
hit int
)
insert into #temp
select '2 ', '50 ' union all select '3 ', '12 ' union all select '4 ', '30 ' union all select '5 ', '105 ' union all select '6 ', '54 ' union all select '7 ', '30 '
select * from #temp
select count(*)+1 from #temp t
where exists(select 1 from #temp where hit <t.hit and id=6)
--------------
2
[解决办法]
名次生成方式1,hit重复时合并名次
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb
(
id int,
hit int
)
insert into tb(id,hit) values(2, 50)
insert into tb(id,hit) values(3, 12)
insert into tb(id,hit) values(4, 30)
insert into tb(id,hit) values(5, 105)
insert into tb(id,hit) values(6, 54)
insert into tb(id,hit) values(7, 30)
SELECT *,Px=(SELECT COUNT(DISTINCT hit) FROM tb WHERE hit > = a.hit)
FROM tb a
where id = 6
ORDER BY hit
drop table tb
/*result
id hit Px
----------- ----------- -----------
6 54 2
（所影响的行数为 1 行）
*/
