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语句
- ORA:00918出错,请求帮忙解决
- 请教C++连接ORACLE的问题
- 请教这样的sql怎么写?
- 编译存储过程死机,如何查看资源情况了??
- windows2003server+oracle10g用http://server:5500/em进入后进行备份时,出现主机身份证明问题.
- VB.net能连接ORACLE;ASP.net却不能连接ORACLE。这是怎么回事????
- 我的Oracle为什么无法创建数据库-ora-12560 tns 监听错误
- 请问主键和唯一索引究竟有什么区别,是否能够告知。。。。
- ORA-03113 错误是什么?
- 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。