sql - 在子查询中使用主查询中的列

Question

有没有办法从主查询中实时获取列，并在子查询中使用它？

类似这样的:(在子查询中使用 A.item)

SELECT item1, * 
FROM TableA A 
INNER JOIN 
(
    select * 
    from TableB B 
    where A.item = B.item
) on A.x = B.x;

我需要修改此现有查询。以前是有效的，但是现在数据库发生了变化，我需要做一些修改，添加一些比较。正如你所看到的，有很多 JOINS，其中之一就是子查询。我需要添加主查询(例如表 T0)的列与子查询的比较(如下所示:T6.UnionAll_Empresa = T0.UnionALl_Empresa)

Select T0.UnionAll_Empresa,<STUFF>

from [UNION_ALL_BASES]..OINV T0 with (nolock)
inner join [UNION_ALL_BASES]..INV6 T1 with (nolock) on t0.DocEntry = t1.DocEntry and t0.UnionAll_Empresa = t1.UnionAll_Empresa
inner join

(
select 
t1.CompanyID,
T2.CompanyDb,
t1.OurNumber,
T6.BankCode,
T6.BankName,
T3.[Description] Situation,
T1.[Status],
T5.Descrption nomeStatus,
T1.Origin,
T1.DocEntry,
T1.DocType,
T1.ControlKey,
T1.CardCode,
T4.[Description] ContractBank,
T1.PayMethodCode,
T1.DueDate,
T1.DocDate,
T1.InstallmentID,
T1.InstallmentValue,
T1.Correction,
T1.InterestContractural,
T1.FineContract,
T1.ValueAbatment,
T1.ValueDiscount,
T1.ValueFineLate,
T1.ValueInterestDaysOfLate,
T1.OtherIncreases,
T1.ValueInWords,
T1.ValueDocument,
T1.DigitalLine,
T1.Document
from [IntegrationBank]..BillOfExchange T1 with (nolock)
    inner join [InterCompany2]..CompanyHierarchy T2 with (nolock) on T1.CompanyID = T2.ID
    left join [IntegrationBank]..BillOfExchangeSituation T3 with (nolock) on T1.Situation = T3.ID 
    inner join [IntegrationBank]..ContractBank T4 with (nolock) on T1.ContractBank = T4.ID 
    inner join [IntegrationBank]..BoeStatus T5 with (nolock) on T1.[Status] = T5.ID 
    inner join [UNION_ALL_BASES]..ODSC T6 with (nolock) on T4.BankKey = T6.AbsEntry and **T6.UnionAll_Empresa = T0.UnionALl_Empresa** --I need to do this 
where T1.[Status] <> 5 
and T2.CompanyDb = **T0.UnionAll_Empresa** --I need to do this
) TBI on (T1.DocEntry = TBI.DocEntry and T1.InstlmntID = TBI.InstallmentID and TBI.DocType = T1.ObjType )
inner join [UNION_ALL_BASES]..OCTG T2 on T0.GroupNum = T2.GroupNum and T0.UnionAll_Empresa = T2.UnionAll_Empresa
inner join [UNION_ALL_BASES]..OSLP T3 on T0.SlpCode = T3.SlpCode and T0.UnionAll_Empresa = T3.UnionAll_Empresa
where not exists (select 1
        from [UNION_ALL_BASES]..RIN1 A with (nolock) 
                inner join [UNION_ALL_BASES]..ORIN B with (nolock) on A.DocEntry = B.DocEntry and A.UnionAll_Empresa = B.UnionAll_Empresa
        where A.BaseEntry = T0.DocEntry
        and   B.SeqCode = ''1'' )

Answer 1

您可以使用 OUTER APPLY

   SELECT  *
    FROM    tbl1
            OUTER APPLY ( SELECT TOP 1
                                    currency_id,
                                    SUM(taxrate) AS taxrate
                          FROM      tbl2
                          WHERE     wuptr.currency_id = tbl1.currency_id
                          GROUP BY  tbl2.currencyid
                        )