我有以下我似乎无法弄清楚.我正在尝试获取帐户中的帐户管理员列表,他们的开始/结束日期以及在一行中接管帐户的新帐户管理员.
例：
DECLARE @accountManagerListing TABLE
(
accountNumber INT,accountManager VARCHAR(8),accountManagerStartDate DATE,accountManagerEndDate DATE
)
INSERT INTO @accountManagerListing (accountNumber,accountManager,accountManagerStartDate,accountManagerEndDate)
VALUES (1,'asmith','01/01/2001','01/31/2001'),(1,'bsmith','02/01/2001','03/01/2002'),'csmith','03/02/2002','03/10/2002'),'dsmith','03/11/2002','06/01/2017'),'esmith','06/02/2017','08/17/2018'),(2,'fsmith','02/11/2018','06/01/2018'),'gsmith','06/02/2018',null)
预期成绩：
Account Number Old Account Manager New Account Manager Start Date End Date 1 asmith 01/01/2001 01/31/2001 1 asmith bsmith 02/01/2001 03/01/2002 1 bsmith csmith 03/02/2002 03/10/2002 1 csmith dsmith 03/11/2002 06/01/2017 1 dsmith esmith 06/02/2017 08/17/2018 2 fsmith 02/11/2018 06/01/2018 2 fsmith gsmith 06/02/2018 NULL
解决方法
使用lag()：
select a.*,lag(accountManager) over (partition by accountnumber order by accountManagerStartDate) as OldAccountManager from @accountManagerListing a;
