create table #t(name varchar(10),number int,mtime datetime)
insert into #t values('aaa',6,'2010-02-10 19:47:59.137')
insert into #t values('bbb',5,'2009-12-28 09:03:35.750')
insert into #t values('ccc',3,'2009-12-26 09:12:35.670')
insert into #t values('ddd',3,'2010-01-26 20:53:49.800')
insert into #t values('eee',2,'2009-11-30 17:23:26.823')
insert into #t values('fff',2,'2009-12-26 14:58:39.383')
select * from #t按照number降序,mtime升序,查处name排在第几名,返回排名的值(int)和mtime,
执行速度越快越好,我写了3个,觉得速度不够快,谢谢。
insert into #t values('aaa',6,'2010-02-10 19:47:59.137')
insert into #t values('bbb',5,'2009-12-28 09:03:35.750')
insert into #t values('ccc',3,'2009-12-26 09:12:35.670')
insert into #t values('ddd',3,'2010-01-26 20:53:49.800')
insert into #t values('eee',2,'2009-11-30 17:23:26.823')
insert into #t values('fff',2,'2009-12-26 14:58:39.383')
select * from #t按照number降序,mtime升序,查处name排在第几名,返回排名的值(int)和mtime,
执行速度越快越好,我写了3个,觉得速度不够快,谢谢。
select row= row_number() over(order by number desc ,mtime asc) from tb
from @t
/*
(1 行受影响)
name number mtime rank
---------- ----------- ----------------------- --------------------
aaa 6 2010-02-10 19:47:59.137 1
bbb 5 2009-12-28 09:03:35.750 2
ccc 3 2009-12-26 09:12:35.670 3
ddd 3 2010-01-26 20:53:49.800 4
eee 2 2009-11-30 17:23:26.823 5
fff 2 2009-12-26 14:58:39.383 6
*/
declare @name varchar(10)
set @name='eee'
select count(1) as 排名,b.mtime
from #t a
join (select number,mtime from #t where name=@name) b
on a.number>b.number or (a.number=b.number and a.mtime<b.mtime)
group by b.mtime/**
排名 mtime
----------- ------------------------------------------------------
4 2009-11-30 17:23:26.823(所影响的行数为 1 行)
**/
create table #t(name varchar(10),number int,mtime datetime)
insert into #t values('aaa',6,'2010-02-10 19:47:59.137')
insert into #t values('bbb',5,'2009-12-28 09:03:35.750')
insert into #t values('ccc',3,'2009-12-26 09:12:35.670')
insert into #t values('ddd',3,'2010-01-26 20:53:49.800')
insert into #t values('eee',2,'2009-11-30 17:23:26.823')
insert into #t values('fff',2,'2009-12-26 14:58:39.383')
select row=(select count(1) from #t where
number>a.number or (number=a.number and mtime<a.mtime))+1,* from #t a
/*
row name number mtime
----------- ---------- ----------- -----------------------
1 aaa 6 2010-02-10 19:47:59.137
2 bbb 5 2009-12-28 09:03:35.750
3 ccc 3 2009-12-26 09:12:35.670
4 ddd 3 2010-01-26 20:53:49.800
5 eee 2 2009-11-30 17:23:26.823
6 fff 2 2009-12-26 14:58:39.383(6 行受影响)*/