日期:2014-05-16 浏览次数:20492 次
if object_id('tempdb..#a') is not null drop table #a
create table #a
( user1 varchar(5) , friend2 varchar(5), content varchar, [time] int)
insert into #a
select 'A','B',1,1 union all
select 'A','B',2,1 union all
select 'A','C',3,1 union all
select 'A','B',4,1 union all
select 'A','B',5,1 union all
select 'A','C',4,1 union all
select 'A','B',5,1 union all
select 'A','C',7,1
---开始查询
select user1 , friend2 , content, time from (
select * ,row_number() over ( partition by user1,friend2 order by m desc ) as num
from (select *,row_number() over ( order by getdate()