sql - 在 SQL 查询条件下添加重复行

Question

假设有两个数据行:

with
 data_table (title, val_a, val_b) as ( 
    select 'A', 1, 1 from dual union all 
    select 'B', 1, 2 from dual
 ) 
 select *
   from data_table;

我想创建一个 SQL 查询，它在 VAL_A <> VAL_B 时复制一行，返回三行，如:

| TITLE | VAL_A | VAL_B |  MARKER    |
--------------------------------------
|  'A'  |  1    |  1    | 'SINGLE'   |
|  'B'  |  1    |  2    | 'MULTIPLE' |
|  'B'  |  1    |  2    | 'MULTIPLE' |

并且从大表中选择两次是不合适的data_table (联合所有，...)。

Answer 1

您可以使用不等式的自连接:

with
 data_table (title, val_a, val_b) as ( 
    select 'A', 1, 1 from dual union all 
    select 'B', 1, 2 from dual
 ) 
 select d1.*, nvl2(d2.title,'MULTIPLE','SINGLE') as marker
   from data_table d1
   left join data_table d2 on d1.val_a <> d1.val_b
  where d2.val_a is null
     or ( d2.val_a is not null and d2.val_a = d2.val_b );

Demo