SQL:将子查询转换为联接:如何在嵌套联接where子句中引用外部表? [英] SQL: Turn a subquery into a join: How to refer to outside table in nested join where clause?

问题描述

我试图将我的子查询更改为联接，在联接中它仅选择子查询中的一条记录.似乎为找到的每条记录运行了子查询，需要花费一分钟的时间来执行:

I am trying to change my sub-query in to a join where it selects only one record in the sub-query. It seems to run the sub-query for each found record, taking over a minute to execute:

select afield1, afield2, (
    select top 1 b.field1
    from   anothertable as b
    where  b.aForeignKey = a.id
    order by field1
) as bfield1
from sometable as a

如果我仅尝试选择相关记录，它将不知道如何在嵌套选择中绑定a.id.

If I try to only select related records, it doesn't know how to bind a.id in the nested select.

select afield1, afield2, bfield1
from   sometable a left join (
    select top 1 id, bfield, aForeignKey 
    from   anothertable
    where  anothertable.aForeignKey = a.id
    order by bfield) b on
       b.aForeignKey = a.id
-- Results in the multi-part identifier "a.id" could not be bound

如果我对嵌套的where子句中的值进行硬编码，那么选择的持续时间将从60秒降至5秒以下.在不处理内部表中的每条记录的同时，有人对如何联接两个表有何建议?

If I hard code values in the nested where clause, the select duration drops from 60 seconds to under five. Anyone have any suggestions on how to join the two tables while not processing every record in the inner table?

我最终添加了

left outer join (
    select *, row_number() over (partition by / order by) as rank) b on
    b.aforeignkey = a.id and b.rank = 1

对于2200万行，从约50秒缩短到8秒.

went from ~50 seconds to 8 for 22M rows.

推荐答案

尝试一下:

WITH qry AS
(
    SELECT afield1, 
           afield2, 
           b.field1 AS bfield1,
           ROW_NUMBER() OVER(PARTITION BY a.id ORDER BY field1) rn
      FROM sometable a LEFT JOIN anothertable b
        ON b.aForeignKey = a.id
)
SELECT *
  FROM qry
 WHERE rn = 1

这篇关于SQL:将子查询转换为联接:如何在嵌套联接where子句中引用外部表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！