有表 A
Item Datetime Cus
A 4/29/1983 MG
A 5/31/1998 SG
A 2/28/2002 MG
B 10/5/1983 MC
B 8/31/1998 MF
B 12/31/2004 MC 如何得到表 B
Item Datetime OldCus NewCus
A 5/28/1998 MG SG
A 3/1/2002 SG MG
B 8/31/1998 SL MF
B 12/29/2004 MF MC及按Item和Datetime排序, 依次用最小时间的填充OldCus, 当条记录的Cus为 NewCus
Item Datetime Cus
A 4/29/1983 MG
A 5/31/1998 SG
A 2/28/2002 MG
B 10/5/1983 MC
B 8/31/1998 MF
B 12/31/2004 MC 如何得到表 B
Item Datetime OldCus NewCus
A 5/28/1998 MG SG
A 3/1/2002 SG MG
B 8/31/1998 SL MF
B 12/29/2004 MF MC及按Item和Datetime排序, 依次用最小时间的填充OldCus, 当条记录的Cus为 NewCus
--try
select *,OldCus=(select top 1 Cus from A where Item=tmp.Item order by [Datetime])
from A as tmp
INSERT @a SELECT 'A','4/29/1983','MG'
UNION ALL SELECT 'A', '5/31/1998', 'SG'
UNION ALL SELECT 'A','2/28/2002','MG'
UNION ALL SELECT 'B', '10/5/1983','MC'
UNION ALL SELECT 'B','8/31/1998','MF'
UNION ALL SELECT 'B', '12/31/2004','MC' SELECT Item,
[DATETIME],
OldCus=(SELECT Cus FROM @a WHERE item=a.item AND [datetime]=(SELECT MAX([DATETIME]) da FROM @a WHERE Item=a.Item AND [DATETIME]<A.[DATETIME])),
NewCus=Cus
FROM @a a
WHERE EXISTS(SELECT 1 FROM @a WHERE item=a.item AND [DATETIME]<A.[DATETIME])
--result
/*Item DATETIME OldCus NewCus
-------------------- ------------------------------------------------------ -------------------- --------------------
A 1998-05-31 00:00:00.000 MG SG
A 2002-02-28 00:00:00.000 SG MG
B 1998-08-31 00:00:00.000 MC MF
B 2004-12-31 00:00:00.000 MF MC(所影响的行数为 4 行)*/
from A a1 inner join a a2
on a1.Item=a2.Item
and (select count(1) from a where item=a1.item and [Datetime] <=a1.[Datetime])=(select count(1) from a where item=a2.item and [Datetime] <=a2.[Datetime])+1