复杂的SQL连接子查询，上面有1条记录

I'm trying to write a query that looks something like below.

我正在尝试编写一个查询，看起来如下所示。

select t1.t1c1, t1.t1c2, t2.t2c3, t2.t2c4
from table1 t1 
left outer join (select top 1 t2c1, t2c2, t2c3, t2c4 from table2 
                 where t2c5 in (select t3c1 from table3 t3
                                where **t3c2 = t1.t1c2 and t3c3 = t1.t1c3**) t2
           on t1.t1c1 = t2.t2c1 and t1.t1c2 = t2.t2c2

What SQL Server does not allow is the highlighted text above - i.e. referencing the table1's columns in the table3 sub query. Is there a way to achieve this? I understand that this might not be the most optimal way, is there any other way to achieve this?

SQL Server不允许的是上面突出显示的文本，即在table3子查询中引用table1的列。有办法做到这一点吗?我知道这可能不是最优的方法，还有其他方法可以实现吗?

1 个解决方案

#1

You seem to exactly want outer apply. I think it would look like this:

你似乎确实想要外部应用。我想应该是这样的:

select t1.t1c1, t1.t1c2, t2.t2c3, t2.t2c4
from table1 t1 outer apply
     (select top 1 t2c1, t2c2, t2c3, t2c4
      from table2 
      where t2c5 in (select t3c1
                     from table3 t3
                     where t3c2 = t1.t1c2 and t3c3 = t1.t1c3
                    ) and
            t1.t1c1 = t2.t2c1 and t1.t1c2 = t2.t2c2
     ) t2;

APPLY is a lot like using a correlated subquery, except it goes in the FROM clause and can return multiple columns and multiple rows.

APPLY非常类似于使用关联子查询，但是它进入FROM子句，可以返回多个列和多个行。

Note: You should be using ORDER BY when you use TOP.

注意:当你使用TOP时，你应该使用ORDER。

#1