sql - 选择减去两个表的查询，如果子项中有更多数据，则不应考虑它

Question

create table oms (run_date date,ban varchar2(10),DVR_IND char(1));

create table enab (run_date date,ban varchar2(10),DVR_IND char(1));

主表

    insert into oms values(trunc(sysdate),'1111111111','Y');  
    insert into oms values(trunc(sysdate),'1111111111','Y'); 
    insert into oms values(trunc(sysdate),'1111111111','Y'); 
    insert into oms values(trunc(sysdate),'2222222222','Y'); 
    insert into oms values(trunc(sysdate),'3333333333','Y'); 
    insert into oms values(trunc(sysdate),'3333333333','N'); 
    insert into oms values(trunc(sysdate),'4444444444','Y'); 
    insert into oms values(trunc(sysdate),'4444444444','Y'); 
    insert into oms values(trunc(sysdate),'4444444444','Y'); 
    insert into oms values(trunc(sysdate),'4444444444','Y'); 
    insert into oms values(trunc(sysdate),'4444444444','N'); 
    insert into oms values(trunc(sysdate),'4444444444','N'); 

和子表:

    insert into enab values(trunc(sysdate),'1111111111','Y');  
    insert into enab values(trunc(sysdate),'2222222222','Y'); 
    insert into enab values(trunc(sysdate),'3333333333','Y'); 
    insert into enab values(trunc(sysdate),'3333333333','Y'); 
    insert into enab values(trunc(sysdate),'3333333333','N'); 
    insert into enab values(trunc(sysdate),'4444444444','Y'); 
    insert into enab values(trunc(sysdate),'4444444444','N'); 
    insert into enab values(trunc(sysdate),'5555555555','N'); 

查询应该返回这些记录:

    insert into oms values(trunc(sysdate),'1111111111','Y'); 
    insert into oms values(trunc(sysdate),'1111111111','Y'); 
    insert into oms values(trunc(sysdate),'4444444444','Y'); 
    insert into oms values(trunc(sysdate),'4444444444','Y'); 
    insert into oms values(trunc(sysdate),'4444444444','Y'); 

所有记录的结果应该有 DVR_IND= 'Y'

1. 对于 ban 1111111111，OMS 中的总计数为 3，enab 中为 1，结果为 (3-1) = 2
2. 对于 ban 2222222222，OMS 中的总计数为 1，enab 中的总计数为 1，结果为 (1-1) = 0
3. 对于 ban 3333333333，OMS 中的总计数为 1，enab 中为 2，结果为 (1-2) = 0(因为我们需要考虑 oms 表和 dvr_ind='Y')
4. 对于禁令 4444444444，OMS 中的总计数为 4，enab 中为 1，结果为 (4-1) = 3

所以总计数 = 2+0+0+3 = 5(我想得到这五个记录)

Answer 1

使用 row_number() 枚举行并使用值和此计数器左连接表:

select run_date, ban, dvr_ind
    from (
      select o.*, o.rowid, 
          row_number() over (partition by run_date, ban, dvr_ind order by null) rn 
        from oms o) o
    left join (
      select e.*, e.rowid,
          row_number() over (partition by e.run_date, e.ban, e.dvr_ind order by null) rn 
        from enab e) e using (run_date, ban, dvr_ind, rn)
    where e.rowid is null;

^{SQLFiddle demo}

在您的 ban='4444444444' 示例中，oms 中有 6 行，enab 中有 2 行匹配(不像您在描述中写的那样是 4 和 1)，所以输出略有不同。

---

编辑: 我没有注意到，您只对 dvr_ind = 'Y' 的行感兴趣。因此，在两个子查询(或最后一行)中添加 where dvr_ind = 'Y':

select run_date, ban, dvr_ind
    from (
      select o.*, o.rowid,
          row_number() over (partition by run_date, ban, dvr_ind order by null) rn
        from oms o where dvr_ind = 'Y') o
    left join (
      select e.*, e.rowid,
          row_number() over (partition by e.run_date, e.ban, e.dvr_ind order by null) rn
        from enab e where dvr_ind = 'Y') e using (run_date, ban, dvr_ind, rn)
    where e.rowid is null