日期:2014-05-17  浏览次数:21003 次

left outer join 问题
select   Salesinvoices.SalesInvoiceID,SalesInvoiceItems.SalesInvoiceItemID,Salesinvoices.Companyid,Salesinvoices.Departmentid,Salesinvoices.Payer,Salesinvoices.Billdate,SalesinvoiceItems.Materialid,Salesinvoices.Employeeid,SaleQuantity,TaxInPrice,TaxInValue,TaxControlCode,
              0,0,0,0,Arapclearitems.*
from   SalesInvoiceItems
          inner   join   SalesInvoices   on   SalesInvoices.Salesinvoiceid=SalesInvoiceItems.Salesinvoiceid
          left   outer   join   ARAPClearItems
                          inner   join   ARAPClears   on   ARAPClears.Clearid   =ARAPClearItems.Clearid    
            on   nvl(ARAPClearItems.ARAPbillID, '   ')   =SalesInvoiceItems.Salesinvoiceid   and   nvl(ARAPClearItems.BillItemID, '   ')=SalesInvoiceItems.SalesinvoiceItemID   and   nvl(ARAPClearitems.Invsign, '   ')   =   '1 '
          where   companyid= '010101 '   and   substring(billdate,1,6)= '200703 '   and   payer= '00001964 '
为什么与
select   Salesinvoices.SalesInvoiceID,SalesInvoiceItems.SalesInvoiceItemID,Salesinvoices.Companyid,Salesinvoices.Departmentid,Salesinvoices.Payer,Salesinvoices.Billdate,SalesinvoiceItems.Materialid,Salesinvoices.Employeeid,SaleQuantity,TaxInPrice,TaxInValue,TaxControlCode,
              0,0,0,0,Arapclearitems.*
from   SalesInvoiceItems
          inner   join   SalesInvoices   on   SalesInvoices.Salesinvoiceid=SalesInvoiceItems.Salesinvoiceid
          left   outer   join   ARAPClearItems
                         
            on   nvl(ARAPClearItems.ARAPbillID, '   ')   =SalesInvoiceItems.Salesinvoiceid   and   nvl(ARAPClearItems.BillItemID, '   ')=SalesInvoiceItems.SalesinvoiceItemID   and   nvl(ARAPClearitems.Invsign, '   ')   =   '1 '
          where   companyid= '010101 '   and   substring(billdate,1,6)= '200703 '   and   payer= '00001964 '
执行结果不一样?下面的可以出来六条纪录。但是上面的没有纪录?就是因为left   outer   join没有匹配上吗?但是left   outer   join   不应该影响纪录的条数吧~

------解决方案--------------------
太长,up
------解决方案--------------------
太长,up
------解决方案--------------------
太长,up
------解决方案--------------------
左关联,右关联搞清楚,出了问题也没有大问题,仔细调试一下吧
------解决方案--------------------
这也太长了吧
------解决方案--------------------
上面的语句有问题:
inner join ARAPClears ON ARAPClears.Clearid =ARAPClearItems.Clearid

ON

nvl(ARAPClearItems.ARAPbillID, ' ') =SalesInvoiceItems.Salesinvoiceid and nvl

怎么又出来个on?
------解决方案--------------------
好好看看内、外,左、右关联的区别吧.再看看SQL语句的优化问题.
------解决方案--------------------