LEFT JOIN最多返回一行以获得1对多关系

I have a query in SQL Server where I'm doing multiple left joins. At one point I get to:

我在SQL Server中有一个查询，我正在进行多个左连接。有一次，我得到：

...LEFT JOIN foo on a.id = foo.id AND foo.code = 100

Up to that join statement in the query, a.id is not repeated; there's only 1 row for every a.id. But table a and foo have a 1-to-many relationship, so after that join statement I get multiple rows with the same a.id.

在查询中最多连接语句，a.id不会重复;每个a.id只有一行。但是表a和foo具有1对多的关系，所以在连接语句之后我得到多行具有相同的a.id.

Table foo will always have multiple records where foo.id = a.id, but it may or may not have a record where foo.id = a.id AND foo.code = 100. If it does, there will be only 1 such record. So, what I want to see in the final query results is just 1 row for every a.id; if foo has a record where f.id = a.id and f.code = 100, then I want to see the date values from f.date1 and f.date2 from that record. If foo doesn't have such a record, then I just want to see NULL values for f.date1 and f.date2.

表foo将始终有多个记录，其中foo.id = a.id，但它可能有也可能没有记录，其中foo.id = a.id AND foo.code = 100.如果有，则只有1个这样的记录记录。所以，我想在最终查询结果中看到的每个a.id只有一行;如果foo有一个f.id = a.id和f.code = 100的记录，那么我想从该记录中看到f.date1和f.date2的日期值。如果foo没有这样的记录，那么我只想看f.date1和f.date2的NULL值。

Edit from the comments:

从评论中编辑：

It turns out that table foo actually can have multiple rows where a.id = foo.id and foo.code = 100.

事实证明，表foo实际上可以有多行，其中a.id = foo.id和foo.code = 100。

1 个解决方案

#1

One possible way to do it is to use OUTER APPLY:

一种可能的方法是使用OUTER APPLY：

SELECT
    a.id
    ,OA_foo.date1
    ,OA_foo.date2
FROM
    a
    OUTER APPLY
    (
        SELECT
            -- TOP(1)
            foo.date1
            ,foo.date2
        FROM foo
        WHERE
            foo.id = a.id
            AND foo.code = 100
        -- ORDER BY ...
    ) AS OA_foo
;

If you know for sure that the filter foo.id = a.id AND foo.code = 100 will always return only one row (or no rows), then you can leave the query as is.

如果您确定过滤器foo.id = a.id AND foo.code = 100将始终只返回一行（或没有行），那么您可以保持查询不变。

If it is possible that nested query returns more than row and you want to leave only one row, then specify appropriate ORDER BY and TOP(1).

如果嵌套查询可能返回的行多于行，并且您只想留下一行，则指定适当的ORDER BY和TOP（1）。

#1