仅将连续记录中的重复值替换为NULL

小编典典

仅将连续记录中的重复值替换为NULL

sql

说我有两个表
Tran（Tran_Id，Tran_Name）TFlag（Tran_Id，Flag）

我的查询结果是这样的：

TRAN_ID TRAN_NAME   FLAG    
-------------------------
101     Lend        A   
101     Lend        B   
101     Lend        C   
101     Lend        D   
102     Borrow      E   
101     Lend        F   
101     Lend        G

我希望输出是这样的：

TRAN_ID TRAN_NAME   FLAG
-------------------------
101     Lend        A   
(null)  (null)      B   
(null)  (null)      C   
(null)  (null)      D   
102     Borrow      E   
101     Lend        F   
(null)  (null)      G

阅读 158

2021-05-16

共1个答案

小编典典

在SQL Server和Oracle中，您都可以使用LAG分析功能。甲骨文：

WITH data (tran_id, tran_name, flag) AS (
    SELECT  01,   'Lend', 'A' FROM DUAL UNION ALL
    SELECT 101,   'Lend', 'B' FROM DUAL UNION ALL
    SELECT 101,   'Lend', 'C' FROM DUAL UNION ALL
    SELECT 101,   'Lend', 'D' FROM DUAL UNION ALL
    SELECT 102, 'Borrow', 'E' FROM DUAL UNION ALL
    SELECT 101,   'Lend', 'F' FROM DUAL UNION ALL
    SELECT 101,   'Lend', 'G' FROM DUAL 
)
SELECT
    NVL2(keep, tran_id, null) tran_id,
    NVL2(keep, tran_name, null) tran_name,
    flag
FROM (
    SELECT
        tran_id, tran_name, flag,
        CASE WHEN LAG(tran_id) OVER (ORDER BY flag) <> tran_id OR LAG(tran_name) OVER (ORDER BY flag) <> tran_name THEN 1 END keep
    FROM
        data)

2021-05-16