SQL：将子查询变成联接：如何在嵌套联接 where 子句中引用外部表？

发布于 2024-11-24 20:02:28 字数 918 浏览 0 评论 0原文

我正在尝试将子查询更改为联接，其中它仅选择子查询中的一条记录。它似乎为每个找到的记录运行子查询，执行时间超过一分钟：

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 。

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 秒。有人对如何连接两个表而不处理内表中的每条记录有什么建议吗？

编辑：

我最终将

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

22M 行的时间从约 50 秒增加到 8 秒。

原文

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

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

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?

EDIT:

I ended up adding

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

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

著墨染雨君画夕 2024-12-01 20:02:28

试试这个：

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

Try this:

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

回复收藏 0 原文

萌酱 2024-12-01 20:02:28

试试这个

select afield1, 
afield2, 
bfield1 
from sometable a 
left join 
(select top 1 id, bfield, aForeignKey from  anothertable where  aForeignKey in(a.id)  order by bfield) b on b.aForeignKey = a.id

Try this

select afield1, 
afield2, 
bfield1 
from sometable a 
left join 
(select top 1 id, bfield, aForeignKey from  anothertable where  aForeignKey in(a.id)  order by bfield) b on b.aForeignKey = a.id

回复收藏 0 原文

~没有更多了~