日期:2014-05-17  浏览次数:20552 次

求写一个查询语句
SQL code
uid        tradeid                paytime                    itemnum
781        159376785910132        2012-09-24 21:34:18        1
400        159376785910187        2012-09-24 21:34:15        1
400        159784222283590        2012-09-24 21:25:23        1
341        179404231308162        2012-09-24 20:34:27        1
400        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)