如何取最近两个时间值

[qq_32212069]

select distinct select distinct a.tid ,s_name,max( a.paytime)
from dbo.OrderInfo2015 a,dbo.OrderDetailedInfo2015 b,as_good c,as_faren d
where a.tid=b.tid and b.numiid=s_id and fid=s_shid


tid订单号，s_name 物品信息，paytime时间，我要取相同物品的最后一个订单的时间和前一个订单的时间，求。

[xdashewan]

OVER PARTITION BY分组排序取前2

[中国风]

;WITH t
AS
(
SELECT  a.tid ,s_name,a.paytime,ROW_NUMBER()OVER(PARTITION BY c.s_id ORDER BY paytime DESC) AS rn
from dbo.OrderInfo2015 a
,dbo.OrderDetailedInfo2015 b
,as_good c
,as_faren d
where a.tid=b.tid and b.numiid=s_id and fid=s_shid 
)
SELECT a.*,b.paytime AS prepaytime FROM t AS a LEFT JOIN t AS b ON a.s_id=b.s_id AND a.rn=b.rn-1 WHERE a.rn=1

[道素]

我的想法基本和roy_88的一致，但说实话，没弄清楚你的数据结构，相关四表的是不是如下含义： dbo.OrderInfo2015 a --主订单，定义tid dbo.OrderDetailedInfo2015 b -订单明细，其中主要有tie,和产品 ,as_good c 产品基础数据 as_faren 人员表

select * FROM (  
    select a.tid ,s_name,a.paytime,ROW_NUMBER()OVER(PARTITION BY s_name ORDER BY paytime desc) AS SeqNo
    from dbo.OrderInfo2015 a,dbo.OrderDetailedInfo2015 b,as_good c,as_faren d
    where a.tid=b.tid and b.numiid=s_id and fid=s_shid
) t WHERE t.SeqNo<3

------ 例子 -------
;WITH a(tid,s_id,paydate)AS (
	SELECT 1,'A',GETDATE() UNION
	SELECT 1,'B',GETDATE() UNION
	SELECT 2,'C',dateadd(d,1,GETDATE()) UNION
	SELECT 2,'D',dateadd(d,1,GETDATE()) UNION
	SELECT 3,'A',dateadd(d,-1,GETDATE()) UNION
	SELECT 3,'C',dateadd(d,71,GETDATE()) UNION
	SELECT 4,'A',null 
	
)
SELECT * FROM (
SELECT *,ROW_NUMBER()OVER(PARTITION BY s_id ORDER BY paydate desc) AS SeqNo FROM a
) t WHERE SeqNo<3

结果,需要越小就越新
tid	s_id	paydate	SeqNo
1	A	2016-01-28 17:02:57.950	1
3	A	2016-01-27 17:02:57.950	2
1	B	2016-01-28 17:02:57.950	1
3	C	2016-04-08 17:02:57.950	1
2	C	2016-01-29 17:02:57.950	2
2	D	2016-01-29 17:02:57.950	1