SQL：列出行之间的差异（在列中显示）

您可以取消配对的列，只过滤不同的列。

    
      select id, fatherid, field, o before, n after
  from (
    select n.id, n.fatherid, n.a na, o.a oa, n.b nb, o.b ob,
           to_char(n.c) nc, to_char(o.c) oc,
           to_char(n.d) nd, to_char(o.d) od
      from t n join t o on n.fatherid = o.id)
  unpivot ((n, o) for field in ((na, oa) as ‘A’, (nb, ob) as ‘B’,
                                (nc, oc) as ‘C’, (nd, od) as ‘D’))
  where n <> o or (n is null and o is not null) or (n is not null and o is null)
  order by id, field
</code>
  

    
          ID   FATHERID  FIELD  BEFORE    AFTER
  2457       5678  A      Mark      Chris
  2457       5678  D      20170804  20180402
  4554       1234  B      Doe       Dee
  4554       1234  C      15        25
</code>
  

有很多列（大约400个）……

只是为了让你知道如何检查只有4列（这是为Oracle）：

    
      WITH full AS (
  SELECT
    o.ID,
    o.A, o.B, o.C, o.D,
    n.A AS newA, n.B AS newB, n.C AS newC, n.D AS newD
  FROM tablename o
  INNER JOIN tablename n ON n.FatherID = o.ID
  WHERE o.FatherID = -1
)
SELECT ID, Field, Before, After FROM(
  SELECT
    ID,
    ‘A’ As Field,
    CASE WHEN A <> newA THEN 1 ELSE 0 END AS changed,
    A AS Before,
    newA AS After
  FROM full
  UNION
  SELECT
    ID,
    ‘B’ As Field,
    CASE WHEN B <> newB THEN 1 ELSE 0 END AS changed,
    B AS Before,
    newB AS After
  FROM full
  UNION
  SELECT
    ID,
    ‘C’ As Field,
    CASE WHEN C <> newC THEN 1 ELSE 0 END AS changed,
    C AS Before,
    newC AS After
  FROM full
  UNION
  SELECT
    ID,
    ‘D’ As Field,
    CASE WHEN D <> newD THEN 1 ELSE 0 END AS changed,
    D AS Before,
    newD AS After
  FROM full
)
WHERE changed = 1
</code>