在不使用子查询的情况下编写查询

I have two tables, A and B.

我有两张桌子,A和B.

They are joined by a common field called id. Table B also has a field post_code and a date attached to that post_code, meaning the first time that post_code was entered into the database.

它们由一个名为id的公共字段连接在一起。表B还有一个字段post_code和一个附加到该post_code的日期,这意味着post_code第一次输入到数据库中。

I need to select the latest post code and join it to a row on table A WITHOUT a sub-query.

我需要选择最新的邮政编码并将其连接到表A上的一行而不用子查询。

I've got a solution with a sub-query but it's taking far too long

我有一个子查询的解决方案,但它花了太长时间

TIA

1 个解决方案

#1

You can try using APPLY

您可以尝试使用APPLY

Create Table #A(Id int)

Create Table #B(Id int, AId int, PostCode VARCHAR(10), DateAdded DATE)

Insert into #A
    Values(1),(2)


Insert into #B
    Values(1,1,'NW1', GETDATE()-100),
          (2,1,'S20', GETDATE()-200)

Select *
From #A A
    CROSS APPLY(SELECT TOP 1 B.*
                FROM #B B
                WHERE A.Id = B.AId
                ORDER BY DateAdded DESC) Latest