SQL Server 2017：编写查询标记符合规则的卡交易序列

规则说明 #

• 序列以 RespCode = '57' 且 RespStatus = 'D'（拒付） 的交易作为起始
• 序列以起始交易之后第一个 RespStatus = 'A'（批准） 的交易结束
• 序列中间可包含RespCode不同但RespStatus仍为'D'的交易
• 注意：仅标记以RespCode='57'的拒付交易为起始的序列内所有交易，非此类起始的拒付交易无需标记

测试数据表及数据 #

CREATE TABLE #temptable ( [ID] int, [Datetime] datetime, Card varchar(23), [RespCode] varchar(25), [RespStatus] char(1) )
INSERT INTO #temptable
VALUES
( 11377422, N'2023-04-19T20:53:24', '111111XXXXXX1111', '00', 'A' ), 
( 11823828, N'2023-04-29T00:06:48', '111111XXXXXX1111', '00', 'A' ), 
( 12229230, N'2023-05-10T04:14:56', '111111XXXXXX1111', '00', 'A' ), 
( 12761727, N'2023-05-23T05:49:00', '111111XXXXXX1111', '00', 'A' ), 
( 12949136, N'2023-05-28T22:33:25', '111111XXXXXX1111', '00', 'A' ), 
( 13336176, N'2023-06-06T00:23:11', '111111XXXXXX1111', '00', 'A' ), 
( 13478042, N'2023-06-09T02:18:06', '111111XXXXXX1111', '00', 'A' ), 
( 13801436, N'2023-06-18T19:55:52', '111111XXXXXX1111', '00', 'A' ), 
( 14442178, N'2023-07-02T00:17:37', '111111XXXXXX1111', '00', 'A' ), 
( 14583163, N'2023-07-06T22:40:14', '111111XXXXXX1111', '00', 'A' ), 
( 14698189, N'2023-07-09T22:44:22', '111111XXXXXX1111', '00', 'A' ), 
( 15107992, N'2023-07-18T04:20:51', '111111XXXXXX1111', '00', 'A' ), 
( 16129455, N'2023-08-18T23:22:18', '111111XXXXXX1111', '57', 'D' ), 
( 16203188, N'2023-08-20T18:21:32', '111111XXXXXX1111', '57', 'D' ), 
( 16254102, N'2023-08-21T02:59:09', '111111XXXXXX1111', '51', 'D' ), 
( 16343718, N'2023-08-25T21:04:59', '111111XXXXXX1111', '57', 'D' ), 
( 4983081, N'2022-09-05T17:04:58', '111111XXXXXX1111', '00', 'A' ), 
( 5621620, N'2022-10-06T18:20:53', '111111XXXXXX1111', '00', 'A' ), 
( 5762366, N'2022-10-12T16:18:47', '111111XXXXXX1111', '00', 'A' ), 
( 5870930, N'2022-10-17T22:01:09', '111111XXXXXX1111', '00', 'A' ), 
( 6169728, N'2022-10-29T16:38:20', '111111XXXXXX1111', '00', 'A' ), 
( 6300325, N'2022-11-03T19:25:42', '111111XXXXXX1111', '57', 'D' ), 
( 6405161, N'2022-11-07T15:54:50', '111111XXXXXX1111', '00', 'A' ), 
( 6557420, N'2022-11-12T14:46:59', '111111XXXXXX1111', '00', 'A' ), 
( 6972655, N'2022-11-26T17:23:09', '111111XXXXXX1111', '57', 'D' ), 
( 7429597, N'2022-12-11T17:24:10', '111111XXXXXX1111', '00', 'A' ), 
( 7429817, N'2022-12-11T17:15:13', '111111XXXXXX1111', '54', 'D' ), 
( 7539293, N'2022-12-14T01:04:45', '111111XXXXXX1111', '00', 'A' ), 
( 7650241, N'2022-12-19T18:08:22', '111111XXXXXX1111', '57', 'D' ), 
( 8386080, N'2023-01-18T17:28:29', '111111XXXXXX1111', 'N7', 'D' ), 
( 8683916, N'2023-01-26T01:28:30', '111111XXXXXX1111', '57', 'D' ), 
( 8725093, N'2023-01-28T22:41:50', '111111XXXXXX1111', '51', 'D' ), 
( 8863541, N'2023-02-01T17:33:23', '111111XXXXXX1111', '51', 'D' ), 
( 9359202, N'2023-02-16T17:13:26', '111111XXXXXX1111', '00', 'A' ), 
( 9573895, N'2023-02-22T08:45:36', '111111XXXXXX1111', '00', 'A' ), 
( 9708832, N'2023-02-26T00:15:45', '111111XXXXXX1111', '57', 'D' ), 
( 9931305, N'2023-03-03T17:53:16', '111111XXXXXX1111', '51', 'D' ), 
( 9985985, N'2023-03-06T17:42:56', '111111XXXXXX1111', '51', 'D' ), 
( 10245315, N'2023-03-16T04:04:34', '111111XXXXXX1111', '51', 'D' ), 
( 10345800, N'2023-03-20T19:40:11', '111111XXXXXX1111', '00', 'A' ), 
( 10386960, N'2023-03-09T22:17:58', '111111XXXXXX1111', '00', 'A' ), 
( 10630165, N'2023-03-26T01:18:03', '111111XXXXXX1111', '57', 'D' ), 
( 10974576, N'2023-04-05T03:12:51', '111111XXXXXX1111', '00', 'A' ), 
( 11111336, N'2023-04-10T02:45:24', '111111XXXXXX1111', '00', 'A' )

解决方案（SQL查询） #

通过CTE分层处理，先排序交易、识别序列分组，再确定序列边界，最终标记目标交易：

WITH sorted_transactions AS (
    -- 按卡号+时间排序，给每条交易分配行号
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Card ORDER BY Datetime) AS rn
    FROM #temptable
),
sequence_groups AS (
    -- 累计符合起始条件的交易，生成唯一序列ID
    SELECT 
        st.*,
        SUM(CASE WHEN RespCode = '57' AND RespStatus = 'D' THEN 1 ELSE 0 END) 
            OVER (PARTITION BY Card ORDER BY rn) AS sequence_id
    FROM sorted_transactions st
),
sequence_boundaries AS (
    -- 确定每个序列的起始和结束行号
    SELECT 
        sg.Card,
        sg.sequence_id,
        MIN(sg.rn) AS start_rn,
        -- 取起始后第一个批准交易的行号，无则设为最大行号+1
        COALESCE(
            MIN(CASE WHEN sg.RespStatus = 'A' AND sg.rn > MIN(sg.rn) THEN sg.rn END),
            (SELECT MAX(rn) FROM sorted_transactions WHERE Card = sg.Card) + 1
        ) AS end_rn
    FROM sequence_groups sg
    WHERE sg.RespCode = '57' AND sg.RespStatus = 'D'
    GROUP BY sg.Card, sg.sequence_id
)
-- 最终标记符合条件的交易
SELECT 
    st.ID,
    st.Datetime,
    st.Card,
    st.RespCode,
    st.RespStatus,
    CASE 
        WHEN EXISTS (
            SELECT 1 FROM sequence_boundaries sb 
            WHERE sb.Card = st.Card 
              AND st.rn >= sb.start_rn 
              AND st.rn < sb.end_rn
        ) THEN 1 
        ELSE 0 
    END AS IsInTargetSequence
FROM sorted_transactions st
ORDER BY st.Card, st.rn;

逻辑说明 #

1. sorted_transactions：统一交易排序规则，用行号简化位置判断
2. sequence_groups：通过累计起始交易数量，将同属一个序列的交易归为同一sequence_id
3. sequence_boundaries：锁定每个序列的起始点和结束点，明确序列范围
4. 最终查询：通过行号匹配序列范围，标记目标交易

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