- SQL code
uid tradeid paytime itemnum781 159376785910132 2012-09-24 21:34:18 1400 159376785910187 2012-09-24 21:34:15 1400 159784222283590 2012-09-24 21:25:23 1341 179404231308162 2012-09-24 20:34:27 1400 179404231306845 2012-09-24 21:39:16 1
需求:按时间倒序(paytime desc)、获取最新的3条(top 3)、这最新的3条uid不能重复(distinct uid)
谢谢大虾
------解决方案--------------------
declare @table1 table (uid int,tradeid varchar(20),paytime datetime, itemnum int)
insert into @table1
select 781 ,'159376785910132', '2012-09-24 21:34:18', 1 union all
select 400 ,'159376785910187', '2012-09-24 21:34:15', 1 union all
select 400 ,'159784222283590', '2012-09-24 21:25:23', 1 union all
select 341 ,'179404231308162', '2012-09-24 20:34:27', 1 union all
select 400 ,'179404231306845', '2012-09-24 21:39:16', 1
--需求:按时间倒序(paytime desc)、获取最新的3条(top 3)、这最新的3条uid不能重复(distinct uid)
select top 3 uid,tradeid,paytime,itemnum from
(
select ROW_NUMBER() over(partition by uid order by paytime desc) as rowno,* from @table1
) a
where rowno=1 order by paytime desc
------解决方案--------------------
- SQL code
SELECT TOP 3 * FROM (SELECT ROWNUM=ROW_NUMBER()OVER(PARTITION BY [UID] ORDER BY PAYTIME DESC),* FROM TABLE01 ) T WHERE T.ROWNUM=1
------解决方案--------------------
- SQL code
--多谢7楼指正select top 3 * from 表 a where exists(select uid,max(paytime) paytime,max(itemnum) from 表 where a.uid=uid and a.paytime=paytime group by uid order by paytime desc)