优化SQL查询：统计发票头字段不一致的发票数量

我们有一张表source_table1，其中invnum（发票号）、invamount（发票总金额）、descr（发票描述）属于发票头字段，这些字段在同一张发票的所有行中应保持一致，但目前存在每行重复存储的情况。需要统计存在头字段不一致的发票数量（比如示例数据中invnum=446的发票，其descr字段在某行出现了不一致的值）。

表创建语句 #

create table source_table1 (invnum, invamount,descr,linetype, amount, linenumber) as
  select 123,120,'desc1', 'ITEM', 100, 1 from dual union all
  select 123,120,'desc1', 'TAX' ,  20, 2 from dual union all
  select 446,220,'desc2', 'ITEM', 100, 1 from dual union all
  select 446,220,'desc2', 'ITEM', 100, 2 from dual union all
  select 446, 220,'desc22','TAX' ,  20, 3 from dual  union all
  select 500, 220,'desc3','ITEM' ,  220, 1 from dual

现有查询语句 #

select count(1) 
from (
    select count(invnum)
    from (
        select invnum,count(*) 
        from source_table1 
        group by invnum, invamount,descr
    ) 
    group by invnum
    having count(invnum) > 1
)

优化后的查询方案 #

以下几种方案逻辑更简洁，执行效率也更高：

方案一：窗口函数快速检测 #

仅扫描一次原表，通过窗口函数计算每个发票号下invamount和descr的唯一值数量，直接筛选出存在不一致的发票：

select count(distinct invnum)
from (
    select invnum,
           count(distinct invamount) over (partition by invnum) as amt_distinct_cnt,
           count(distinct descr) over (partition by invnum) as desc_distinct_cnt
    from source_table1
)
where amt_distinct_cnt > 1 or desc_distinct_cnt > 1

方案二：简化分组逻辑 #

利用Oracle支持多字段组合distinct的特性，直接统计每个发票下唯一的头字段组合数，超过1则说明存在不一致：

select count(*)
from (
    select invnum
    from source_table1
    group by invnum
    having count(distinct (invamount, descr)) > 1
)

方案三：关联查询定位（大数据量场景） #

如果表数据量极大，且invnum字段有索引，可通过关联查询快速定位存在不一致的发票，减少后续计算量：

select count(distinct s1.invnum)
from source_table1 s1
where exists (
    select 1
    from source_table1 s2
    where s2.invnum = s1.invnum
      and (s2.invamount != s1.invamount or s2.descr != s1.descr)
)

内容的提问来源于stack exchange，提问作者Confused