我想从日志表中获取前 5 条记录,其中"批准日期"像 NULL 一样更改为值,反之亦然。日期值无关紧要,但顺序很重要。
ApprovedDate ChangeDate changeByUser
NULL 2019-12-09 06:40:15.437 vaisakh
NULL 2019-12-09 06:42:31.563 vaisakh
NULL 2019-12-09 06:42:33.140 vaisakh
NULL 2019-12-09 07:03:54.660 vaisakh
2019-12-09 07:05:29.800 2019-12-09 07:05:29.817 vaisakh
2019-12-09 07:05:29.800 2019-12-09 07:05:38.707 vaisakh
NULL 2019-12-09 07:09:33.160 vaisakh
NULL 2019-12-09 07:09:42.440 vaisakh
NULL 2019-12-09 09:38:19.757 vaisakh
2019-12-09 09:41:42.977 2019-12-09 09:41:43.243 Raveendran
在这种情况下,我想要第一条记录和第 5 条记录(有人批准了数据,这就是为什么一个值(,然后第 7 条记录值为空,有人拒绝了它。
我尝试使用递归 CTE 它正在工作,但对于大型记录来说,巨大的性能问题
DECLARE @today DATETIME = GETDATE();
with RESULT (CIPApprovedDate,ChangeDate,changeByUser,legalId,depth)AS(
SELECT TOP 1 CIPApprovedDate, ChangeDate, changeByUser, legalId,1
FROM LegalEntityExtensionLog
WHERE legalId= 2688518
ORDER BY ChangeDate ASC
union ALL
select
L.CIPApprovedDate, L.ChangeDate, L.changeByUser, L.legalId,ct.depth+1
FROM LegalEntityExtensionLog L INNER JOIN Result CT
on L.legalId=CT.legalId AND L.changeDate>CT.changeDate
AND ISNULL(L.CIPApprovedDate,@today) <> ISNULL(CT.CIPApprovedDate,@today)
)select * from Log where ChangeDate in(select MIN(ChangeDate) from Result group by depth)
您可以像这样提取转换记录:
select ApprovedDate, ChangeDate, changeByUser
from (
select
l.*,
lag(ApprovedDate) ver(partition by LegalId order by ChangeDate) lagApprovedDate
from LegalEntityExtensionLog l
) t
where
(lagApprovedDate is null and ApprovedDate is not null)
or (lagApprovedDate is not null and ApprovedDate is null)
这将显示ApprovedDate
从null
转换为非null
值(或相反(的记录。
递归 CTE 通过每次递归访问表有时可能会很慢。
但是是否需要获得空/非空开关?
这将得到相同的结果
-- Sample data CREATE TABLE LegalEntityExtensionLog ( Id int identity(1,1) primary key, ApprovedDate datetime, ChangeDate datetime not null, ChangeByUser varchar(42) not null, LegalId int not null ); insert into LegalEntityExtensionLog (ApprovedDate, ChangeDate, ChangeByUser, LegalId) values (NULL, '2019-12-09 06:40:15.437', 'vaisakh', 2688518) ,(NULL, '2019-12-09 06:42:31.563', 'vaisakh', 2688518) ,(NULL, '2019-12-09 06:42:33.140', 'vaisakh', 2688518) ,(NULL, '2019-12-09 07:03:54.660', 'vaisakh', 2688518) ,('2019-12-09 07:05:29.800', '2019-12-09 07:05:29.817', 'vaisakh', 2688518) ,('2019-12-09 07:05:29.800', '2019-12-09 07:05:38.707', 'vaisakh', 2688518) ,(NULL, '2019-12-09 07:09:33.160', 'vaisakh', 2688518) ,(NULL, '2019-12-09 07:09:42.440', 'vaisakh', 2688518) ,(NULL, '2019-12-09 09:38:19.757', 'vaisakh', 2688518) ,('2019-12-09 09:41:42.977', '2019-12-09 09:41:43.243', 'Raveendran', 2688518) ;
查询:
WITH RESULT AS ( SELECT * , ROW_NUMBER() OVER (PARTITION BY LegalId ORDER BY ChangeDate) AS rn , LAG(ApprovedDate) OVER (PARTITION BY LegalId ORDER BY ChangeDate) AS prevApprDt FROM LegalEntityExtensionLog WHERE legalId = 2688518 AND ChangeDate >= cast('2019-12-09' AS DATE) ) SELECT ApprovedDate, ChangeDate, ChangeByUser, LegalId FROM RESULT WHERE ( RN = 1 OR (ApprovedDate IS NULL AND prevApprDt IS NOT NULL) OR (ApprovedDate IS NOT NULL AND prevApprDt IS NULL) ) ORDER BY ChangeDate GO
结果:
批准日期 |变更日期 |更改用户 |合法身份证 :------------------ |:------------------ |:----------- |------:空|2019-09-12 06:40:15 |维萨克 |2688518 2019-09-12 07:05:29 |2019-09-12 07:05:29 |维萨克 |2688518空|2019/09/12 07:09:33 |维萨克 |2688518 2019-09-12 09:41:42 |2019-09-12 09:41:43 |拉文德兰 |2688518
数据库<>在这里小提琴