Oracle DB中两行的逐行比较

Question

我需要编写一个查询来逐列比较数据库中两行之间的差异(即:查找差异)。例如：

row1: 10 40 sometext 24
row2: 10 25 sometext 24

执行查询后，应该只显示有差异的字段(即:第二个字段)。

以下是我迄今所做的工作：

select table1.column1, table1.column2, table1.column3, table1.column4 
from table1 
where somefield in (field1, field2);

上面的查询将向我显示两行，一列在上面，如下所示: 10 40 sometext 24 10 25

然后，我必须手动进行比较，这需要很长的时间，b/c，行包含了很多列。

因此，我的问题是:如何编写一个只显示有差异的列的查询？

谢谢

Answer 1

使用UNPIVOT子句(请参阅http://www.oracle-developer.net/display.php?id=506)将列转换为行，然后过滤出相同的行(使用GROUP BY HAVING COUNT，最后使用PIVOT获取不同列的行。

Answer 2

要轻松地做到这一点，您需要查询表的元数据以获取每一行。您可以使用以下代码作为脚本。

将define table_name替换为表名和define yes_drop_it = NO。将原始WHERE语法放入where_clause中。比较逻辑总是比较为where子句返回的前两行。

whenever sqlerror exit failure rollback;

set linesize 150

define test_tab_name = tst_cf_cols
define yes_drop_it = YES
define order_by = 1, 2
define where_clause = 1 = 1
define tab_owner = user

<<clearfirst>> begin
  for clearout in (
    select 'drop table ' || table_name as cmd
    from all_tables 
    where owner = &&tab_owner and table_name = upper('&&test_tab_name')
    and '&&yes_drop_it' = 'YES'
  ) loop
    execute immediate clearout.cmd;
    execute immediate '
        create table &&test_tab_name as
        select 10 as column1, 40 as column2, ''sometext'' as column3, 24 as column4 from dual
        union all
        select 10 as column1, 25 as column2, ''sometext'' as column3, 24 as column4 from dual
    ';
  end loop;
end;
/

column cfsynt format a4000 word_wrap new_value comparison_syntax

with parms as (select 'parmquery' as cte_name, 'row_a' as corr_name_1, 'row_b' as corr_name_2 from dual)
select 
  'select * from (select ' || LISTAGG(cfcol || ' AS cf_' || trim (to_char (column_id, '000')) || '_' || column_name 
, chr(13) || ', ') WITHIN GROUP (order by column_id) 
  || chr(13) || ' from (select * from parmquery where row_number = 1) ' || corr_name_1 
  || chr(13) || ',  (select * from parmquery where row_number = 2) ' || corr_name_2
  || chr(13) || ') where ''DIFFERENT'' IN (' || LISTAGG ('cf_' || trim (to_char (column_id, '000')) || '_' || column_name, chr(13) || ', ') within group (order by column_id) || ')' 
  as cfsynt
from parms, (
  select 
    'decode (' || corr_name_1 || '.' || column_name || ', ' || corr_name_2
    || '.' || column_name || ', ''SAME'', ''DIFFERENT'')'  
    as cfcol,
    column_name,
    column_id
  from 
    parms, 
    all_tab_columns
  where
    owner = &&tab_owner and table_name = upper ('&&test_tab_name')
);

with parmquery as (select rownum as row_number, vals.* from (
  select * from &&test_tab_name 
  where &&where_clause
  order by &&order_by
  ) vals 
) &&comparison_syntax
;