基于SQL临时表#TblName查找最新OnHold变更操作的用户名

测试数据SQL #

IF OBJECT_ID('tempdb..#TblName') IS NOT NULL
    BEGIN
        DROP TABLE #TblName
    END

CREATE TABLE #TblName (
    Key1 varchar(50)
    ,DateStamped date
    ,LogText nvarchar(max)
)

INSERT INTO #TblName VALUES ('PF1','2021-09-01','Gabriela.Santa 18:26:25  OnHold: False -> True  OnHoldDate:  -> 9/1/2021  OnHoldReasonCode:  -> NEWPDCN')
INSERT INTO #TblName VALUES ('PF1','2022-12-26','NhatCuong.Nguyen 08:21:18  OnHold: True -> False  OnHoldDate: 12/23/2022 ->   OnHoldReasonCode: NoCnf ->   OnHoldComment_c: 3D for Chemical miliing change, need to update CM BDWG ->   NhatCuong.Nguyen 15:10:42  OnHold: False -> True  OnHoldDate:  -> 12/26/2022  OnHoldReasonCode:  -> NoCnf  OnHoldComment_c:  -> 3D for Chemical miliing change, need to update CM BDWG')
INSERT INTO #TblName VALUES ('PF1','2023-01-05','NhatCuong.Nguyen 13:37:42  OnHold: True -> False  OnHoldDate: 12/26/2022 ->   OnHoldReasonCode: NoCnf ->   OnHoldComment_c: 3D for Chemical miliing change, need to update CM BDWG ->   Quoc.Phan 14:36:24  OnHold: False -> True  OnHoldDate:  -> 1/5/2023  OnHoldReasonCode:  -> NoCnf  OnHoldComment_c:  -> 3D for Chemical miliing change, need to update CM BDWG  Nguyen.Anh 14:55:42  OnHold: True -> False  OnHoldDate: 1/5/2023 ->   OnHoldReasonCode: NoCnf ->   OnHoldComment_c: 3D for Chemical miliing change, need to update CM BDWG ->   Quoc.Phan 14:57:29  OnHold: False -> True  OnHoldDate:  -> 1/5/2023  OnHoldReasonCode:  -> NoCnf  OnHoldComment_c:  -> 3D for Chemical miliing change, need to update CM BDWG')
INSERT INTO #TblName VALUES ('PF1','2022-12-23','ThiThanh.Nguyen 10:12:22  AnalysisCode:  -> MachSO  Quoc.Phan 16:11:22  OnHold: False -> True  OnHoldDate:  -> 12/23/2022  OnHoldReasonCode:  -> NoCnf  OnHoldComment_c:  -> 3D for Chemical miliing change, need to update CM BDWG')
INSERT INTO #TblName VALUES ('PF1','2021-09-16','Valentin.Opris 12:08:52  OnHold: True -> False  OnHoldDate: 9/1/2021 ->   OnHoldReasonCode: NEWPDCN ->   Bogdan.Stefanescu 17:19:26  OnHold: False -> True  OnHoldDate:  -> 9/16/2021  OnHoldReasonCode:  -> NEWPDCN  Jothimani.Alagappan 17:29:53  ChangeRequestReason_c: CM_V0107 Spirit Sunshine - Move ST in house -> CM_V0330 SPIRIT SUNSHINE - Update Manufacturing Drawing PDCN review')

需求说明 #

从#TblName表的LogText字段中，筛选出所有执行了OnHold: False -> True操作的记录，找到其中时间最晚的事务对应的用户名，最终返回Key1、DateStamped和Username三个字段。

解决方案SQL #

WITH SplitLogs AS (
    -- 拆分每个LogText中的操作条目，识别用户名开头的操作记录
    SELECT 
        t.Key1,
        t.DateStamped,
        -- 提取完整操作片段
        SUBSTRING(t.LogText, s.StartPos, 
                  COALESCE(NULLIF(CHARINDEX(' ', t.LogText, CHARINDEX(' ', t.LogText, s.StartPos)+8), 0), LEN(t.LogText)+1) - s.StartPos) AS Operation,
        -- 提取用户名
        SUBSTRING(t.LogText, s.StartPos, CHARINDEX(' ', t.LogText, s.StartPos) - s.StartPos) AS Username,
        -- 拼接完整操作时间（日期+时分秒）
        CAST(t.DateStamped AS DATETIME) + CAST(SUBSTRING(t.LogText, CHARINDEX(' ', t.LogText, s.StartPos)+1, 8) AS DATETIME) AS OperationTime
    FROM #TblName t
    -- 生成辅助拆分位置，匹配用户名格式（包含.）的起始点
    CROSS APPLY (
        SELECT 1 AS StartPos
        UNION ALL
        SELECT CHARINDEX(' ', t.LogText, pos) + 1
        FROM (
            SELECT NUMBER + 1 AS pos
            FROM master.dbo.spt_values
            WHERE TYPE = 'P' AND NUMBER < LEN(t.LogText)
        ) pos_list
        WHERE SUBSTRING(t.LogText, pos, CHARINDEX(' ', t.LogText, pos) - pos) LIKE '%.%'
    ) s(StartPos)
    WHERE SUBSTRING(t.LogText, s.StartPos, CHARINDEX(' ', t.LogText, s.StartPos) - s.StartPos) LIKE '%.%'
),
TargetOperations AS (
    -- 筛选目标操作并按时间倒序排名
    SELECT 
        Key1,
        DateStamped,
        Username,
        OperationTime,
        ROW_NUMBER() OVER(ORDER BY OperationTime DESC) AS rn
    FROM SplitLogs
    WHERE Operation LIKE '%OnHold: False -> True%'
)
-- 获取最新的一条记录
SELECT Key1, DateStamped, Username
FROM TargetOperations
WHERE rn = 1

预期输出 #

内容的提问来源于stack exchange，提问作者TKN