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 是主键
------解决方案--------------------
- SQL code
select distinct invoice_nofrom shipment Awhere exists(select 1 from ship where invoice_no =A.invoice_no and supplier_no=A.supplier_no)
------解决方案--------------------
- SQL code
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
------解决方案--------------------
- SQL code
select distinct invoice_nofrom shipment Awhere not exists(select 1 from ship where invoice_no =A.invoice_no and supplier_no=A.supplier_no)