Мне в основном нужно сделать левое внешнее соединение на 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
Спасибо - я надеюсь, что это имеет смысл
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 включительно.
Я знаю, что этот вопрос старше, но есть другой подход, который, по моему мнению, недоиспользуется:
Вы можете присоединить таблицы к себе и использовать оператор, чтобы найти "самую последнюю" запись.
Ответ
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, если этот сценарий возникает.
Просто догадаться. Я не уверен, что 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;