sql - 两个结果集相减SQL

Question

Result1     Result2
a           a
b           b    
a

如何获得 a通过在这两个结果集之间进行减法。 NOT EXISTS和 NOT IN对于这两个结果集不返回任何内容。但我想要 a被退回。请帮忙!

Answer 1

嗯。 . .这很棘手，因为您想考虑计数。一种方法是:

select r1.col, r1.cnt - coalesce(r2.cnt, 0)
from (select col, count(*) as cnt
      from result1
      group by col
     ) r1 left join
     (select col, count(*) as cnt
      from resuult2
      group by col
    ) r2
    on r1.col = r2.col
where r1.cnt > coalesce(r2.cnt, 0);

这不会完全返回您想要的内容，但可能就足够了。另一种方法是使用 row_number() :

select r1.col
from (select col, row_number() over (partition by col order by col) as seqnum
      from result1
      group by col
     ) r1 left join
     (select col, row_number() over (partition by col order by col) as seqnum
      from resuult2
      group by col
    ) r2
    on r1.col = r2.col and r1.seqnum = r2.seqnum
where r2.col is null;