SQL Server: LEFT OUTER JOIN с TOP 1 для выбора в большинстве строк

Мне в основном нужно сделать левое внешнее соединение на 2 таблицах (CarePlan и Referrals). Проблема в том, что мне нужен новейший реферал. Если он существует, его нормально, если он не делает.

У меня эти 2 запроса 1. присоединяется к таблицам CarePlan/Referral - создает дубликаты планов обслуживания, если есть несколько рефералов для плана обслуживания или вообще не имеет реферальной информации (левое внешнее соединение) 2. выберите верхнюю 1 реферал, основанную на дате, с учетом CarePlanId

Я хотел бы объединить эти 2, чтобы я захватил все планы обслуживания и их рефералы, если он существует, если это так - возьмите только новейший реферал

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

ИЗМЕНИТЬ:

Первый запрос дает мне что-то вроде этого:

CarePlanID    ReferralId     ReferralDate
----------    ----------     ------------
1             1              05/15/12
2             NULL           NULL
1             2              05/10/12  //Old date, dont want this careplan

Второй запрос даст мне реферал с самой новой датой

ReferralId    ReferralDate
----------    ------------
1             05/15/12

Данные перенаправления могут содержать 0 или более рефералов, относящихся к Careplan

ReferralID  CarePlanId    Date
----------  ----------    ----
1           1             05/15/12
2           1             05/10/12

В конечном счете, я хочу получить запрос, который дает мне планы по уходу с рефералами, имеющими самую новую дату, или null для рефералов, если у нее нет этого

вот так:

CarePlanId   ReferralId    ReferralDate
----------   ----------    ------------
1            1             05/15/12
2            NULL          NULL

Спасибо - я надеюсь, что это имеет смысл

Ответ 1

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

Имейте в виду, что это заставляет объединение циклов из-за недостатков оптимизатора вплоть до версии 2014 включительно.

Ответ 2

Я знаю, что этот вопрос старше, но есть другой подход, который, по моему мнению, недоиспользуется:

Вы можете присоединить таблицы к себе и использовать оператор, чтобы найти "самую последнюю" запись.

Ответ

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

результаты:

CP.CarePlanId   R.ReferralId    R.ReferralDate
----------      ----------      ------------
1               1               05/15/12
2               NULL            NULL

Объяснение

Пусть это сломается. Вы в основном говорите, что для каждой реферальной записи (слева внешний) присоединяются к каждой другой реферальной записи, связанной с тем же CarePlanId, но ТОЛЬКО, где есть новый ReferralDate.

Вот запрос без предложения 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

Вот результат этого запроса:

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

Как вы можете видеть, только референтный Id 2 (3-я запись выше) обнаружил "новую" запись для присоединения к реферальной таблице (т.е. идентификатор 1 реферала). Идентификатор реферала 1 (1-я запись выше) НЕ нашел "нового" реферала (для того же CarePlanId).

Итак, имея в виду это, теперь мы просто добавляем предложение 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

и получим:

CP.CarePlanId   R.ReferralId    R.ReferralDate  R_NEWER.ReferralId  R_NEWER.ReferralDate
----------      ----------      ------------    ------------        ------------    
1               1               05/15/12        NULL                NULL
2               NULL            NULL            NULL                NULL

На этом этапе просто удалите столбцы R_NEWER из своего SELECT, поскольку они больше не нужны, и у вас есть свой ответ.

Важно помнить, что "where" применяется после того, как произошли соединения, но оператор ON появляется во время соединения. Чтобы сделать это более понятным для меня, я всегда стараюсь писать SELECT и JOINs и возвращать столбцы из каждой таблицы, с которой я соединяюсь, а затем добавлять предложения WHERE, когда у меня есть четкое представление о том, что возвращается.

Caveat Этот подход отлично работает в большинстве случаев, но можно иметь повторяющиеся строки, если у вас было 2 реферала (для того же CarePlanId) с датой 05/15/12, и эта дата была "самой последней". Чтобы обойти это, вы можете расширить свои соединения для ограничения на основе "самого высокого" ReferralId, если этот сценарий возникает.

Ответ 3

Просто догадаться. Я не уверен, что 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;