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
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.
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
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
精彩评论