select identity(int,1,1) as id,* into #t_1 from 表select * from #t_1
select id1=identity(int,1,1),* into #t from a select * from #t
参考:create table Test ( A_id int, title varchar(10), num int, LastDT datetime ) insert Test(A_id,title,num,LastDT) select 1,'吃饭',80,'2006-8-24 10:37:24' union all select 2,'睡觉',75,'2006-8-26 09:55:09' union all select 3,'看书',80,'2006-8-25 10:37:24' union all select 4,'上网',92,'2006-8-28 11:39:25' union all select 5,'游戏',70,'2006-8-27 11:40:18' --select * from Test order by num desc,lastDT-- 如果LastDT日期值相同的话还是会并列第 几 名的 select *, 排名= ( select count(*) from Test where num > A.num or (num = A.num and LastDT<=A.LastDT) ) from Test A order by 排名drop table Test
into #t_1
from 表select * from #t_1
select * from #t
(
A_id int,
title varchar(10),
num int,
LastDT datetime
)
insert Test(A_id,title,num,LastDT)
select 1,'吃饭',80,'2006-8-24 10:37:24' union all
select 2,'睡觉',75,'2006-8-26 09:55:09' union all
select 3,'看书',80,'2006-8-25 10:37:24' union all
select 4,'上网',92,'2006-8-28 11:39:25' union all
select 5,'游戏',70,'2006-8-27 11:40:18'
--select * from Test order by num desc,lastDT-- 如果LastDT日期值相同的话还是会并列第 几 名的
select *,
排名=
(
select count(*) from Test
where num > A.num
or (num = A.num and LastDT<=A.LastDT)
)
from Test A
order by 排名drop table Test