帮忙写个sql 语句，急

[zhuxiaojun2002]

表一
id t1
1 2014-08-20 11:51:11.117
2 2014-08-20 13:52:11.117
1 2014-08-20 13:53:11.117
3 2014-08-20 12:51:11.117
1 2014-08-20 14:51:11.117
3 2014-08-20 13:51:11.117
2 2014-08-20 14:51:11.117
2 2014-08-20 15:51:11.117

我想得到
id t1 t2(id的上一次时间)
1 2014-08-20 11:51:11.117 ...
2 2014-08-20 13:52:11.117 ...
1 2014-08-20 13:53:11.117 2014-08-20 11:51:11.117
3 2014-08-20 12:51:11.117 ...
1 2014-08-20 14:51:11.117 2014-08-20 13:53:11.117
3 2014-08-20 13:51:11.117 2014-08-20 12:51:11.117
2 2014-08-20 14:51:11.117 2014-08-20 13:52:11.117
2 2014-08-20 15:51:11.117 2014-08-20 14:51:11.117

也就是结果集后面加一个这个ID上次的时间

[zhuxiaojun2002]

在线等啊，实在是不会。动不动我就想用游标了，可是真不想用游标啊！

[jamesrggg]

Declare @TmpData Table( ID Int, Times DateTime) Insert Into @TmpData Select 1,'2014-08-20 11:51:11.117' Union Select 2,'2014-08-20 13:52:11.117' Union Select 1,'2014-08-20 13:53:11.117' Union Select 3,'2014-08-20 12:51:11.117' Union Select 1,'2014-08-20 14:51:11.117' Union Select 3,'2014-08-20 13:51:11.117' Union Select 2,'2014-08-20 14:51:11.117' Union Select 2,'2014-08-20 15:51:11.117' Select ID,Times,(Select MAX(Times) From @TmpData Where Id = A.ID And Times < A.Times Group By ID) From @TmpData A

[还在加载中灬]

SELECT T1.*,T3.t1 AS t2
FROM 表一 T1
	OUTER APPLY(SELECT TOP 1 t1 FROM 表一 T2 WHERE T1.id=T2.id AND T1.t1>T2.t1 ORDER BY T2.t1 DESC)T3

[江南小鱼]

with t(id,t1) as(
select 1, '2014-08-20 11:51:11.117' union all
select 2,'2014-08-20 13:52:11.117' union all
select 1,'2014-08-20 13:53:11.117' union all
select 3,'2014-08-20 12:51:11.117' union all
select 1,'2014-08-20 14:51:11.117' union all
select 3,'2014-08-20 13:51:11.117' union all
select 2,'2014-08-20 14:51:11.117' union all
select 2,'2014-08-20 15:51:11.117'
)
select id,t1,isnull((select MAX(t1) from t where id = x.id and t1 < x.t1 group by id),'') t2 
from t x 

[Tiger_Zhao]

WITH table1(id,t1) AS(
    SELECT 1,'2014-08-20 11:51:11.117' UNION ALL
    SELECT 2,'2014-08-20 13:52:11.117' UNION ALL
    SELECT 1,'2014-08-20 13:53:11.117' UNION ALL
    SELECT 3,'2014-08-20 12:51:11.117' UNION ALL
    SELECT 1,'2014-08-20 14:51:11.117' UNION ALL
    SELECT 3,'2014-08-20 13:51:11.117' UNION ALL
    SELECT 2,'2014-08-20 14:51:11.117' UNION ALL
    SELECT 2,'2014-08-20 15:51:11.117'
),
t AS (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY id ORDER BY t1) rn
  FROM table1
)
    SELECT ta.id, ta.t1, tb.t1
      FROM t ta
 LEFT JOIN t tb
        ON tb.id = ta.id
       AND tb.rn = ta.rn -1
  ORDER BY ta.t1

         id t1                      t1
----------- ----------------------- -----------------------
          1 2014-08-20 11:51:11.117 NULL
          3 2014-08-20 12:51:11.117 NULL
          3 2014-08-20 13:51:11.117 2014-08-20 12:51:11.117
          2 2014-08-20 13:52:11.117 NULL
          1 2014-08-20 13:53:11.117 2014-08-20 11:51:11.117
          1 2014-08-20 14:51:11.117 2014-08-20 13:53:11.117
          2 2014-08-20 14:51:11.117 2014-08-20 13:52:11.117
          2 2014-08-20 15:51:11.117 2014-08-20 14:51:11.117

[卖水果的net]

可以考虑使用 CLR 做一个自定义的聚合，取 上一个值 。

[hery2002]

12和14版本可以使用LAG和LEAD函数 LAG (Transact-SQL) LEAD (Transact-SQL)

USE [AdventureWorks2012];
GO
DECLARE @t_TB TABLE
(
    ROWID TINYINT NOT NULL IDENTITY(1,1)
,   ID TINYINT NOT NULL
,   TRANS_TIME DATETIME
)
;
INSERT INTO @t_TB
    SELECT 1,'2014-08-20 11:51:11.117' UNION ALL
    SELECT 2,'2014-08-20 13:52:11.117' UNION ALL
    SELECT 1,'2014-08-20 13:53:11.117' UNION ALL
    SELECT 3,'2014-08-20 12:51:11.117' UNION ALL
    SELECT 1,'2014-08-20 14:51:11.117' UNION ALL
    SELECT 3,'2014-08-20 13:51:11.117' UNION ALL
    SELECT 2,'2014-08-20 14:51:11.117' UNION ALL
    SELECT 2,'2014-08-20 15:51:11.117'
;

SELECT  
    ROWID
,   ID
,   TRANS_TIME
,   LAG(TRANS_TIME,1,0) OVER (PARTITION BY ID ORDER BY ROWID,ID) AS 'Previous TRANS_TIME'
,   LEAD(TRANS_TIME,1,0) OVER (PARTITION BY ID  ORDER BY ROWID,ID) AS 'Next TRANS_TIME'   
FROM @t_TB 
ORDER BY ROWID