sql 查询两表不相等数据

Question

    select count(1) a from shipment where invoice_no='金华' and supplier_no='SJHD';
    
    select count(1) b from ship where invoice_no='金华' and supplier_no='SJHD';
以上二表查询个数是相等的。
现在我要以此为条件查:
a != b 时的订单号invoice_no 列表.
invoice_no 在表shipment 是主键

Answer 1

select distinct invoice_no
from shipment A
where exists(select 1 from ship where invoice_no =A.invoice_no and supplier_no=A.supplier_no)???LZ给点测试数据和要求结果吧.

Answer 2

select * from 
(
select invoice_no,supplier_no,count(1) as a 
    from shipment group by invoice_no,supplier_no
) a left join 
(
select invoice_no,supplier_no,count(1) as b  
    from ship group by invoice_no,supplier_no
) b 
on a.invoice_no=b.invoice_no and a.supplier_no=b.supplier_no and a.a<>b.b

Answer 3

select distinct invoice_no
from shipment A
where not exists(select 1 from ship where invoice_no =A.invoice_no and supplier_no=A.supplier_no)
掉了not