现有表T_AUDIT数据如下:
ID COL1 COL2 COL3 DATETIME
A 1 2 3 20090510 12:00:000
A 1 3 4 20090510 15:00:000
A 2 5 6 20090510 19:00:000
B 5 3 2 20090510 12:00:000
B 3 3 2 20090510 09:00:000
要求的结果如下:
ID COL1_OLD COL1 COL2_OLD COL2 COL3_OLD COL3 DATETIME
A 1 1 2 3 3 4 20090510 15:00:000
A 1 2 3 5 4 6 20090510 19:00:000
B 3 5 3 3 2 2 20090510 12:00:000即要求出T_AUDIT各列的变更情况
ID COL1 COL2 COL3 DATETIME
A 1 2 3 20090510 12:00:000
A 1 3 4 20090510 15:00:000
A 2 5 6 20090510 19:00:000
B 5 3 2 20090510 12:00:000
B 3 3 2 20090510 09:00:000
要求的结果如下:
ID COL1_OLD COL1 COL2_OLD COL2 COL3_OLD COL3 DATETIME
A 1 1 2 3 3 4 20090510 15:00:000
A 1 2 3 5 4 6 20090510 19:00:000
B 3 5 3 3 2 2 20090510 12:00:000即要求出T_AUDIT各列的变更情况
GO
CREATE TABLE TB(
ID VARCHAR(50)
,COL1 INT
,COL2 INT
,COL3 INT
,[DATETIME] DATETIME
)
INSERT INTO TB
SELECT 'A', 1, 2, 3 ,'20090510 12:00:000' UNION ALL
SELECT 'A', 1, 3, 4 ,'20090510 15:00:000' UNION ALL
SELECT 'A', 2, 5, 6 ,'20090510 19:00:000' UNION ALL
SELECT 'B', 5, 3, 2 ,'20090510 12:00:000' UNION ALL
SELECT 'B', 3, 3, 2 ,'20090510 09:00:000'SELECT
T1.ID,T1.COL1 'COL1_OID',T2.COL1 'COL1'
,T1.COL2 'COL2_OID',T2.COL2 'COL2'
,T1.COL3 'COL3_OID',T2.COL3 'COL3'
,T2.[DATETIME]
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [DATETIME]) 'TID',* FROM TB
) T1
INNER JOIN (
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [DATETIME]) 'TID',* FROM TB
) T2 ON T1.TID=T2.TID-1 AND T1.ID=T2.ID
/*
A 1 1 2 3 3 4 2009-05-10 15:00:00.000
A 1 2 3 5 4 6 2009-05-10 19:00:00.000
B 3 5 3 3 2 2 2009-05-10 12:00:00.000
*/