SQL Server：LEFT OUTER JOIN 与 TOP 1 最多选择一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9592875/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server: LEFT OUTER JOIN with TOP 1 to Select at Most One Row
提问by Jerrold
I basically need to do a left outer join on 2 tables (CarePlan and Referrals) problem is i need the newest Referral If it exists, its ok if it doesnt.
我基本上需要对 2 个表（CarePlan 和 Referrals）进行左外连接，问题是我需要最新的 Referral如果它存在，如果它不存在也可以。
I have these 2 queries 1. joins the CarePlan/Referral tables - creates duplicate careplans if there are multiple referrals for a careplan, or has no referral information at all (left outer join) 2. select the top 1 Referral based on Date, given a CarePlanId
我有这 2 个查询 1. 加入 CarePlan/Referral 表 - 如果一个护理计划有多个推荐，或者根本没有推荐信息，则创建重复的护理计划（左外连接） 2. 根据给定的日期选择前 1 个推荐护理计划 ID
I'd like to combine these 2 so i grab all the careplans and its referrals if it exists, if it does - take only the newest referral
我想将这两个结合起来，以便我获取所有护理计划及其推荐（如果存在），如果存在 - 仅获取最新的推荐
select * from CarePlan c //query 1
left outer join Referral r on
r.CarePlanId = c.CarePlanId
select top 1 * from Referral r //query 2
where r.CarePlanId = '1'
order by ReferralDate desc
EDIT:
编辑：
The first query gives me something like this:
第一个查询给了我这样的东西：
CarePlanID ReferralId ReferralDate
---------- ---------- ------------
1 1 05/15/12
2 NULL NULL
1 2 05/10/12 //Old date, dont want this careplan
The second query will give me the referral with the newest date
第二个查询会给我最新日期的推荐
ReferralId ReferralDate
---------- ------------
1 05/15/12
The Referral data, may have 0 or more referrals belonging to a Careplan
转介数据，可能有 0 个或多个属于 Careplan 的转介
ReferralID CarePlanId Date
---------- ---------- ----
1 1 05/15/12
2 1 05/10/12
Ultimately I want a query that gives me careplans with referrals that have the newest date, or null for referrals if it doesnt have it
最终，我想要一个查询，为我提供具有最新日期的推荐的护理计划，或者如果没有推荐则为 null
like this:
像这样：
CarePlanId ReferralId ReferralDate
---------- ---------- ------------
1 1 05/15/12
2 NULL NULL
Thanks - i hope this makes sense
谢谢 - 我希望这是有道理的
回答by usr
select *
from CarePlan c
outer apply (
select top 1 * --top N rows
from Referral r
where r.CarePlanId = c.CarePlanId --join condition
order by /*fill this in!*/
) x
Be aware that this forces a loop join due to optimizer weaknesses up to and including version 2014.
请注意，由于 2014 版（包括 2014 版）的优化器弱点，这会强制循环连接。
回答by Chad Capra
I know this question is older, but there is another approach which I feel is under-utilized:
我知道这个问题比较老，但我觉得还有另一种方法没有得到充分利用：
You can join tables back to themselves and use an operator to find the "most recent" record.
您可以将表连接回自身并使用运算符查找“最近的”记录。
Answer
回答
SELECT CP.CarePlanId, R.ReferralId, R.ReferralDate
FROM CarePlan CP
LEFT OUTER JOIN Referral R ON R.CarePlanId = CP.CarePlanId
LEFT OUTER JOIN Referral R_NEWER ON R.CarePlanId = R_NEWER.CarePlanId AND R.ReferralDate < R_NEWER.ReferralDate
WHERE R_NEWER.ReferralId IS NULL
results:
结果：
CP.CarePlanId R.ReferralId R.ReferralDate
---------- ---------- ------------
1 1 05/15/12
2 NULL NULL
Explanation
解释
Let's break this down. You are basically saying, for each referral record, (left outer) join to every other referral record that is associated with the same CarePlanId but ONLY where there is a newer ReferralDate.
让我们分解一下。您基本上是说，对于每个推荐记录，（左外）加入与相同 CarePlanId 关联的所有其他推荐记录，但仅在有较新 ReferralDate 的地方。
Here is the query without the where clause (along with some addition info from the R_NEWER table):
这是没有 where 子句的查询（以及来自 R_NEWER 表的一些附加信息）：
SELECT CP.CarePlanId, R.ReferralId, R.ReferralDate, R_NEWER.ReferralId, R.NEWER.ReferralDate
FROM CarePlan CP
LEFT OUTER JOIN Referral R ON R.CarePlanId = CP.CarePlanId
LEFT OUTER JOIN Referral R_NEWER ON R.CarePlanId = R_NEWER.CarePlanId AND R.ReferralDate < R_NEWER.ReferralDate
Here is the result of that query:
这是该查询的结果：
CP.CarePlanId R.ReferralId R.ReferralDate R_NEWER.ReferralId R_NEWER.ReferralDate
---------- ---------- ------------ ------------ ------------
1 1 05/15/12 NULL NULL
2 NULL NULL NULL NULL
1 2 05/10/12 1 05/15/12
As you can see, only referral Id 2 (3rd record above) found a "newer" record to join to in the referral table (i.e. referral Id 1). Referral Id 1 (1st record above) did NOT find a "newer" referral (for the same CarePlanId).
如您所见，只有推荐 ID 2（上面的第 3 条记录）在推荐表中找到了要加入的“较新”记录（即推荐 ID 1）。推荐 ID 1（上面的第一条记录）没有找到“更新的”推荐（对于相同的 CarePlanId）。
So, with this in mind, now we just add the where clause back:
因此，考虑到这一点，现在我们只需添加 where 子句：
SELECT CP.CarePlanId, R.ReferralId, R.ReferralDate, R_NEWER.ReferralId, R.NEWER.ReferralDate
FROM CarePlan CP
LEFT OUTER JOIN Referral R ON R.CarePlanId = CP.CarePlanId
LEFT OUTER JOIN Referral R_NEWER ON R.CarePlanId = R_NEWER.CarePlanId AND R.ReferralDate < R_NEWER.ReferralDate
WHERE R_NEWER.ReferralId IS NULL
and get:
并得到：
CP.CarePlanId R.ReferralId R.ReferralDate R_NEWER.ReferralId R_NEWER.ReferralDate
---------- ---------- ------------ ------------ ------------
1 1 05/15/12 NULL NULL
2 NULL NULL NULL NULL
At this point, just remove your R_NEWER columns from your SELECT as they are not needed any more and you have your answer.
此时，只需从您的 SELECT 中删除您的 R_NEWER 列，因为不再需要它们，您就有了答案。
It is important to remember that the "where" applies AFTER the joins have been occurred, but the ON statement occurs at the time of the join. To make this more understandable for me, I always try to write the SELECTs and JOINs and return columns from each table I am joining and then add my WHERE clauses once I have a clear picture of what is returned.
重要的是要记住，“where”在连接发生后适用，但 ON 语句发生在连接时。为了让我更容易理解，我总是尝试编写 SELECT 和 JOIN 并从我加入的每个表中返回列，然后在我清楚地了解返回的内容后添加我的 WHERE 子句。
CaveatThis approach works great in most cases, but it is possible to have duplicate rows if you had 2 referrals (for the same CarePlanId) with the date of 05/15/12 and that date was the "most recent." To work around that, you could extend your join(s) to limit based on the "highest" ReferralId if that scenario arises.
警告此方法在大多数情况下效果很好，但如果您有 2 个推荐（针对同一个 CarePlanId）且日期为 05/15/12 并且该日期是“最近的”，则可能会有重复的行。为了解决这个问题，如果出现这种情况，您可以根据“最高”ReferralId 扩展您的连接以进行限制。
回答by Aaron Bertrand
Just a guess. I'm not sure if EF is going to have issues with CTE syntax - can you force EF to call a stored procedure so you're not handcuffed by the subset of functionality supported by EF?
只是一个猜测。我不确定 EF 是否会遇到 CTE 语法问题 - 您能否强制 EF 调用存储过程，这样您就不会受到 EF 支持的功能子集的束缚？
;WITH r AS
(
SELECT CarePlanId, MAX(ReferralDate)
FROM dbo.Referrals GROUP BY CarePlanId
)
SELECT * FROM dbo.CarePlan AS c
LEFT OUTER JOIN r
ON r.CarePlanId = c.CarePlanId;
