sql - 如何找到行之间发生的变化？

Question

我有两张表，我需要找出它们之间的区别。

需要的是一个汇总了哪些字段已更改的表格（忽略 id 列）。另外，我不知道哪些列发生了变化。

例如源表[已更改的字段为{name}、{location}；{id} 被忽略]

id || name || location || description

1 || aaaa || ddd || abc

2 || bbbb || eee || abc

例如输出表 [输出 {name}、{location}，因为它们已更改]

Table_name || Field_changed || field_was || field_now

Source table || name || aaaa ||  bbbb

Source table || location || ddd || eee

我试过用lag()；但这只会给我选择的列。最终我想查看所有列中的所有更改，因为我不确定哪些列发生了更改。

另请注意，该表有大约 150 列 - 所以最大的问题之一是如何找到更改的列

标签： sqloracleselect

Answer 1

由于您的表可以在单行中包含多个更改，并且需要在结果中计算为多行，因此我创建了一个查询以分别合并它们，如下所示：

WITH DATAA(ID, NAME, LOCATION, DESCRIPTION)
AS
(SELECT 1, 'aaaa', 'ddd', 'abc' FROM DUAL UNION ALL
SELECT 2, 'bbbb', 'eee', 'abc' FROM DUAL),

-- YOUR QUERY WILL START FROM HERE
CTE AS (SELECT NAME, 
LAG(NAME,1) OVER (ORDER BY ID) PREV_NAME,
LOCATION, 
LAG(LOCATION,1) OVER (ORDER BY ID) PREV_LOCATION,
DESCRIPTION, 
LAG(DESCRIPTION,1) OVER (ORDER BY ID) PREV_DESCRIPTION 
FROM DATAA)
--
SELECT
    'Source table' AS TABLE_NAME,
    FIELD_CHANGED,
    FIELD_WAS,
    FIELD_NOW
FROM
    (
        SELECT
            'Name' AS FIELD_CHANGED,
            PREV_NAME        AS FIELD_WAS,
            NAME   AS FIELD_NOW
        FROM
            CTE
        WHERE
            NAME <> PREV_NAME
        UNION ALL
        SELECT
            'location' AS FIELD_CHANGED,
            PREV_LOCATION        AS FIELD_WAS,
            LOCATION   AS FIELD_NOW
        FROM
            CTE
        WHERE
            LOCATION <> PREV_LOCATION
        UNION ALL
        SELECT
            'description' AS FIELD_CHANGED,
            PREV_DESCRIPTION        AS FIELD_WAS,
            DESCRIPTION   AS FIELD_NOW
        FROM
            CTE
        WHERE
            DESCRIPTION <> PREV_DESCRIPTION
    );

输出：

演示

干杯！！