求写一个查询话语

求写一个查询语句

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) 

 

查看更多 下一篇