一个表中的数据
ID nickname addtime
1 a 2011-12-19 11:11:12
2 b 2011-12-19 11:11:12
1 c 2011-12-19 11:11:10
2 c 2011-12-19 11:11:10
1 a 2011-12-19 11:11:11
2 b 2011-12-19 11:11:11
需求是根据时间倒序，取出每个ID，nickname 的前两条数据。
------解决方案--------------------
- SQL code
;WITH tmp AS ( SELECT * , rn = ROW_NUMBER() OVER ( PARTITION BY id ORDER BY nickname ) FROM tb ) SELECT * FROM tmp WHERE rn <= 2
------解决方案--------------------
- SQL code
select * from (select no=row_number() over(partition by id order by addtime desc),* from tb)t where no<3
------解决方案--------------------
- SQL code
select distinct b.*from tb across apply (select top 2 * from tb where id=a.id order by addtime desc)b