SQL 左连接带有交叉应用的查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14967058/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Left join a query with a cross apply
提问by Masa Rumi
Not really sure whether I can left join the results that I get from a cross apply function:
不确定我是否可以离开加入我从交叉应用函数获得的结果：
select
iv.invoiceno
,w.warehouse
,iv.invoicedate
,iv.invoicedesc
,iv.status
,iv.billingstart as [BillingFrom]
,iv.billingend as [BillingTo]
,CAST((iv.invoicesubtotal) as NUMERIC(38,2))as [Sub-Total]
,CAST((((iv.invoicesubtotal+iv.invoicetax)-iv.invoicetotal)) as NUMERIC(38,2)) as [Discount]
,CAST((iv.invoicetax) as NUMERIC(38,2)) as [SalesTax]
,CAST((iv.invoicetotal) as NUMERIC(38,2)) as [Total]
,d.deal
,d.dealno
,ivt.orderno
,ivt.rectype
,ivt.rectypedisplay
,RTRIM(ivt.masterno) as [ICode]
,ivt.description as [ICodeDesc]
,ivt.fromdate as [From]
,ivt.todate as [To]
,CAST((ivt.days ) as NUMERIC(38,2)) as [days]
,CAST(ivt.qty as NUMERIC(38,0)) as [qty]
,CAST((ivt.cost) as NUMERIC(38,2)) as [UnitCost]
,CAST((ivt.rate) as NUMERIC(38,2)) as [rate]
,CAST((ivt.daysinwk)as NUMERIC(38,2)) as [D/W]
,CAST((ivt.discountamt)as NUMERIC(38,2)) as [Discount]
,CAST((ivt.extended)as NUMERIC(38,2)) as [extended]
,(CASE WHEN ivt.taxable='T' then 'YES' else 'NO' END)as [Taxable]
,ivt.category
,(CASE WHEN (ivt.cost > 0 and ivt.rectype='R') THEN CAST((ivt.revenuebase) as NUMERIC (38,2)) ELSE 0 END) as [subrevenue] from invoice iv
inner join deal d on d.dealid=iv.dealid
inner join invoiceitemview ivt on iv.invoiceid=ivt.invoiceid and iv.invoiceno=ivt.invoiceno
inner join warehouse w on w.locationid=iv.locationid and w.inactive<>'T'
left join category c on c.categoryid=ivt.categoryid
left join ordernoteview n on ivt.orderid=n.orderid and n.billing ='T' where iv.locationid='00009V5H' and iv.invoiceno='H513369' and iv.status in ('CLOSED', 'PROCESSED') and iv.nocharge<>'T' order by iv.invoiceno, iv.invoicedate,c.category,ivt.masterno
I wanted to add a left join with this query:
我想用这个查询添加一个左连接：
select tot.gldate, tot.glno, tot.glacctdesc,
tot.debit,tot.credit,tot.glaccountid from invoice ivt cross apply dbo.funcglforinvoice(ivt.invoiceid, null, null) as tot where ivt.invoiceno='H513369'
but when I do that it gives me way more records then there are supposed to be.
但是当我这样做时，它给了我更多的记录。
This has been executing for a while. Basically the inner cross apply query generates 204 items and I wanted that to be left join with the items from the main query; but I am doing something wrong not sure what exactly. Help will be appreciated.
这已经执行了一段时间。基本上内部交叉应用查询生成 204 个项目，我希望它与主查询中的项目保持连接；但我做错了不知道究竟是什么。帮助将不胜感激。
回答by Kenneth Fisher
Use OUTER APPLY. Also I'm not sure if the ON clause after the OUTER APPLY is really needed. If the invoiceid is the same coming out as going in then probably not.
使用外部应用。此外，我不确定是否真的需要 OUTER APPLY 之后的 ON 子句。如果invoiceid 出来时和进去时一样，那么可能不是。
Select iv.invoiceno, iv.invoiceitem,iv.invoiceno
from invoice iv
inner join deal d
on d.dealid=iv.dealid
inner join invoiceitemview ivt
on iv.invoiceid=ivt.invoiceid and iv.invoiceno=ivt.invoiceno
inner join warehouse w
on w.locationid=iv.locationid and w.inactive<>'T'
left join category c
on c.categoryid=ivt.categoryid
left join ordernoteview n
on ivt.orderid=n.orderid and n.billing ='T'
OUTER APPLY dbo.funcglforinvoice(iv.invoiceid, null, null) as tot
