我试图将我的子查询更改为一个连接,它只选择子查询中的一个记录 . 它似乎为每个找到的记录运行子查询,花费一分钟来执行:
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
从22秒开始,从约50秒变为8秒 .
2 回答
试试这个:
试试这个