select distinct ww.strike_billno from
( select h.strike_billno ,h.stotalprice ,i.pk_corp ,i.cvendormangid from cg_invoice i ,cg_invoice_b h where i.pk_id_invoice = h.pk_id_invoice and i.cvendormangid = '0001551000000000K9BA' and i.pk_corp = '1007' ) ww ,
(select hh.strike_billno ,nvl(sum(hh.itotalprice),0) itotal ,ii.pk_corp ,ii.cvendormangid from cg_invoice ii ,cg_invoice_b hh where ii.pk_id_invoice = hh.pk_id_invoice and ii.cvendormangid = '0001551000000000K9BA' and ii.pk_corp = '1007' group by hh.strike_billno ,ii.pk_corp ,ii.cvendormangid )wq
where wq.strike_billno =ww.strike_billno and wq.pk_corp = ww.pk_corp and wq.cvendormangid = ww.cvendormangid and ww.stotalprice = wq.itotal ; 得到三条数据:CF0905130001 CF0906020003 CF0906090003
select a.vinvoicecode,a.dinvoicedate,c.invcode,c.invname,b.ninvoicenum,b.noriginalcurprice,b.noriginalcurmny
from bd_invbasdoc c,po_invoice a,po_invoice_b b
where a.cinvoiceid=b.cinvoiceid
and b.cbaseid=c.pk_invbasdoc
and a.pk_corp='1007'
and a.cvendormangid='0001551000000000K9BA' and a.vinvoicecode not in ('CF0905130001 ','CF0906020003 ' ,'CF0906090003 ')得到29条数据(NOT IN 此时有效果)
select a.vinvoicecode,a.dinvoicedate,c.invcode,c.invname,b.ninvoicenum,b.noriginalcurprice,b.noriginalcurmny
from bd_invbasdoc c,po_invoice a,po_invoice_b b
where a.cinvoiceid=b.cinvoiceid
and b.cbaseid=c.pk_invbasdoc
and a.pk_corp='1007'
and a.cvendormangid='0001551000000000K9BA' and a.vinvoicecode not in ( select distinct ww.strike_billno from
( select h.strike_billno ,h.stotalprice ,i.pk_corp ,i.cvendormangid from cg_invoice i ,cg_invoice_b h
where i.pk_id_invoice = h.pk_id_invoice and i.cvendormangid = '0001551000000000K9BA' and i.pk_corp = '1007'
) ww ,
(select hh.strike_billno ,nvl(sum(hh.itotalprice),0) itotal ,ii.pk_corp ,ii.cvendormangid from cg_invoice ii ,cg_invoice_b hh
where ii.pk_id_invoice = hh.pk_id_invoice and ii.cvendormangid = '0001551000000000K9BA' and ii.pk_corp = '1007' group by hh.strike_billno ,
ii.pk_corp ,ii.cvendormangid ) wq
where wq.strike_billno =ww.strike_billno and wq.pk_corp = ww.pk_corp and wq.cvendormangid = ww.cvendormangid and ww.stotalprice = wq.itotal
);得到34条数据,NOT IN 没发挥出效果!!!
为什么?
( select h.strike_billno ,h.stotalprice ,i.pk_corp ,i.cvendormangid from cg_invoice i ,cg_invoice_b h where i.pk_id_invoice = h.pk_id_invoice and i.cvendormangid = '0001551000000000K9BA' and i.pk_corp = '1007' ) ww ,
(select hh.strike_billno ,nvl(sum(hh.itotalprice),0) itotal ,ii.pk_corp ,ii.cvendormangid from cg_invoice ii ,cg_invoice_b hh where ii.pk_id_invoice = hh.pk_id_invoice and ii.cvendormangid = '0001551000000000K9BA' and ii.pk_corp = '1007' group by hh.strike_billno ,ii.pk_corp ,ii.cvendormangid )wq
where wq.strike_billno =ww.strike_billno and wq.pk_corp = ww.pk_corp and wq.cvendormangid = ww.cvendormangid and ww.stotalprice = wq.itotal ; 得到三条数据:CF0905130001 CF0906020003 CF0906090003
select a.vinvoicecode,a.dinvoicedate,c.invcode,c.invname,b.ninvoicenum,b.noriginalcurprice,b.noriginalcurmny
from bd_invbasdoc c,po_invoice a,po_invoice_b b
where a.cinvoiceid=b.cinvoiceid
and b.cbaseid=c.pk_invbasdoc
and a.pk_corp='1007'
and a.cvendormangid='0001551000000000K9BA' and a.vinvoicecode not in ('CF0905130001 ','CF0906020003 ' ,'CF0906090003 ')得到29条数据(NOT IN 此时有效果)
select a.vinvoicecode,a.dinvoicedate,c.invcode,c.invname,b.ninvoicenum,b.noriginalcurprice,b.noriginalcurmny
from bd_invbasdoc c,po_invoice a,po_invoice_b b
where a.cinvoiceid=b.cinvoiceid
and b.cbaseid=c.pk_invbasdoc
and a.pk_corp='1007'
and a.cvendormangid='0001551000000000K9BA' and a.vinvoicecode not in ( select distinct ww.strike_billno from
( select h.strike_billno ,h.stotalprice ,i.pk_corp ,i.cvendormangid from cg_invoice i ,cg_invoice_b h
where i.pk_id_invoice = h.pk_id_invoice and i.cvendormangid = '0001551000000000K9BA' and i.pk_corp = '1007'
) ww ,
(select hh.strike_billno ,nvl(sum(hh.itotalprice),0) itotal ,ii.pk_corp ,ii.cvendormangid from cg_invoice ii ,cg_invoice_b hh
where ii.pk_id_invoice = hh.pk_id_invoice and ii.cvendormangid = '0001551000000000K9BA' and ii.pk_corp = '1007' group by hh.strike_billno ,
ii.pk_corp ,ii.cvendormangid ) wq
where wq.strike_billno =ww.strike_billno and wq.pk_corp = ww.pk_corp and wq.cvendormangid = ww.cvendormangid and ww.stotalprice = wq.itotal
);得到34条数据,NOT IN 没发挥出效果!!!
为什么?
解决方案 »
- 从SQL中导数到ORACLE显示科学计数法
- 时间格式,麻烦高手顺便看下
- 为什么ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';在控制台有效,用JDBC桥就无效了呢?
- 请问这条sql语句是什么意思???为什么和常规的不一样???
- 关于oracle和informix之间SQL语句兼容的问题
- 数据不同行部分数据重复,写一个select语句
- add constraint 报错 02293 怎么解决?
- 高分求ORCALE 分页存储过程
- oracle 数据怎么导出,并可以保存成excel文件 ?来者有分,欢迎大家发言,谢谢
- oracle数据库for update
- pl/sql的case when怎么用呢?
- oacle不能连接到数据库问题
SELECT * FROM v$reserved_words
关键字::NEW 和:OLD使用方法和意义,new 只出现在insert和update时,old只出现在update和delete时。在insert时new表示新插入的行数据,update时new表示要替换的新数据、old表示要被更改的原来的数据行,delete时old表示要被删除的数据。注意:在触发器中不能使用commit。