SQL Server - 在子查询中使用主查询中的列

SQL Server - use columns from the main query in the subquery

提问by Jo?o Guilherme

Is there any way to get a column in real time, from a main query, and use it in a subquery?

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

Something like this: (Use A.item in the subquery)

像这样：（在子查询中使用 A.item）

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

Ok, here is the real thing:

好的，这是真实的事情：

I need to modify this existing query. It worked before, but now that the database changed, I need to do some modifications, add some comparisons. As you can see there are a lot of JOINS, and one of them is a subquery. I need to add a comparison from a column from the main query (from the table T0 for example) to the subquery (like this: T6.UnionAll_Empresa = T0.UnionALl_Empresa)

我需要修改这个现有的查询。以前可以用，但是现在数据库变了，我需要做一些修改，添加一些比较。如您所见，有很多 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'' )

回答by mmmmmm

You can user OUTER APPLY

您可以使用 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
                        ) 

回答by JNK

You don't need a subquery for that:

您不需要子查询：

SELECT item1, * 
FROM TableA A 
INNER JOIN 
   TableB B 
     ON A.item = B.item
     AND A.x = B.x;

I can't think of a scenario where you would need to JOINon a subquery with a filter like that where it wouldn't be equivalent to just reference the field directly in the outer query.

我想不出这样的场景，您需要JOIN在子查询上使用这样的过滤器，它不等同于直接在外部查询中引用字段。

You can reference the outer table in the subquery in the WHEREclause, though:

但是，您可以在WHERE子句中的子查询中引用外部表：

SELECT <stuff>
FROM Table t
WHERE EXISTS  (SELECT 1 from TableB B 
               WHERE t.id = b.id)

EDIT

编辑

For your actual code, just change the JOINcriteria to this:

对于您的实际代码，只需将JOIN条件更改为：

) TBI on (T1.DocEntry = TBI.DocEntry
          and T1.InstlmntID = TBI.InstallmentID 
          and TBI.DocType = T1.ObjType
          AND TBI.CompanyDB = T0.UnionAll_Empresa )

回答by Stephen Smuts

If you want to join on to a subquery and "get a column in real-time"/ reference a column from the main query, then there is a trick to doing this.

如果您想加入子查询并“实时获取一列”/从主查询中引用一列，那么有一个技巧可以做到这一点。

You can't access the tables which are outside of the subquery if it's used as an aliased table, in other words, this SQL can never access A:

如果将子查询用作别名表，则无法访问子查询之外的表，换句话说，此 SQL 永远无法访问 A：

...
INNER JOIN 
(
    select * 
    from TableB B 
    where A.item = B.item
) on A.x = B.x;

The way to access A would be like this:

访问 A 的方式是这样的：

SELECT item1, * 
FROM TableA A 
INNER JOIN TableB on TableB.item = TableA.item and TableB.item in
(
    select top 1 B.Item
    from TableB B 
    where A.item = B.item
)

Just ignore the "top 1" piece, I just added that to show that there may a reason for doing a join like this.
So, basically if you want to reference an item from the query in the subquery, just move the subquery to the ON section of a join and use the IN keyword as illustrated above.

只需忽略“前 1”部分，我只是添加了它以表明可能有理由进行这样的连接。
因此，基本上如果您想从子查询中的查询中引用一个项目，只需将子查询移动到连接的 ON 部分并使用 IN 关键字，如上所示。

回答by Mohamed Nagieb

You can do this by naming the tables of the main query and the nested query. For example:

您可以通过命名主查询和嵌套查询的表来做到这一点。例如：

SELECT continent, name, population FROM world x
  WHERE population >= ALL
    (SELECT population FROM world y
        WHERE y.continent=x.continent
          AND population>0)

reference: http://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial

参考：http: //sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial

回答by e_i_pi

Not sure why people are over-complicating this. @JNK is correct that you can move the predicate into the main query. For completeness, I will demonstrate.

不知道为什么人们会过度复杂化这个问题。@JNK 是正确的，您可以将谓词移动到主查询中。为了完整起见，我将演示。

You have two predicates in your subquery that reference T0:

您的子查询中有两个谓词引用T0：

T6.UnionAll_Empresa = T0.UnionAll_Empresa
T2.CompanyDb = T0.UnionAll_Empresa

The first is an INNER JOINpredicate on the table T6, and the second a WHEREclause - these are both "hard" filters, and will filter out results that don't match (unlike a LEFT OUTER JOINwhich will simply set reference to that table's values to NULL).

第一个是INNER JOINtable 上的谓词T6，第二个是WHERE子句 - 这些都是“硬”过滤器，将过滤掉不匹配的结果（不像 aLEFT OUTER JOIN只是将对该表的值的引用设置为NULL）。

Well, since T6.UnionAll_Empresaand T2.CompanyDbboth need to filter against T0.UnionAll_Empresa, then we can simply change the INNER JOINpredicate on T6to this:

好吧，既然T6.UnionAll_Empresa和T2.CompanyDb都需要针对 进行过滤T0.UnionAll_Empresa，那么我们可以简单地将INNER JOIN谓词更改为T6：

T2.CompanyDb = T6.UnionAll_Empresa

Then, we can remove the WHEREclause in the subquery, and we can add this JOINpredicate to TBIin the main query:

然后，我们可以删除WHERE子查询中的子句，我们可以在主查询中添加这个JOIN谓词TBI：

TBI.CompanyDb = T0.UnionAll_Empresa

...making the entire query this:

...使整个查询如下：

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 T2.CompanyDb = T6.UnionAll_Empresa
    where T1.[Status] <> 5 
) TBI on (T1.DocEntry = TBI.DocEntry and T1.InstlmntID = TBI.InstallmentID and TBI.DocType = T1.ObjType and TBI.CompanyDb = T0.UnionAll_Empresa)
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''
)

This is entirely equivalent to what you have, and removes any reference to T0from your subquery.

这完全等同于您所拥有的，并T0从您的子查询中删除了任何引用。

回答by Mike Trelinski

You can also use WITH

你也可以使用 WITH

http://msdn.microsoft.com/en-us/library/ms175972.aspx

http://msdn.microsoft.com/en-us/library/ms175972.aspx