SQL-запрос - Соединение, которое возвращает первые две записи присоединяющейся таблицы

Вот моя попытка - Она должна работать над SQL Server 2005 и SQL Server 2008 (Протестированный на SQL Server 2008) вследствие использования общего выражения таблицы:

WITH CTE AS
(
    SELECT  fkPatientId
          , StatusCode
          -- add more columns here
          , ROW_NUMBER() OVER
    (
    PARTITION BY fkPatientId ORDER BY fkPatientId desc) AS [Row_Number] 
    from PatientStatus
    where fkPatientId in
    (
        select fkPatientId
        from PatientStatus
        group by fkPatientId
        having COUNT(*) >= 2
    )
)
SELECT p.pkPatientId,
    p.FirstName,
    CTE.StatusCode  
FROM [Patient] as p
    INNER JOIN CTE
        ON p.[pkPatientId] = CTE.fkPatientId
WHERE CTE.[Row_Number] = 1 
or CTE.[Row_Number] = 2

Править: Оба из следующих решений требуют этого PatientStatus.StartDate уникально в каждом пациенте.

Традиционный путь (совместимый SQL Server 2000):

SELECT 
  p.pkPatientId,
  p.FirstName,
  p.Surname,
  ps.StatusCode,
  ps.StartDate,
  ps.EndDate
FROM 
  Patient p 
  INNER JOIN PatientStatus ps ON 
    p.pkPatientId = ps.fkPatientId
    AND ps.StartDate IN (
      SELECT TOP 2 StartDate 
      FROM     PatientStatus 
      WHERE    fkPatientId = ps.fkPatientId
      ORDER BY StartDate  /* DESC (to switch between first/last records) */
    )
WHERE 
  EXISTS (
    SELECT   1 
    FROM     PatientStatus
    WHERE    fkPatientId = p.pkPatientId
    GROUP BY fkPatientId
    HAVING   COUNT(*) >= 2
  )
ORDER BY 
  ps.fkPatientId, 
  ps.StartDate

Более интересная альтернатива (необходимо было бы попробовать, как хорошо она работает в сравнении):

SELECT 
  p.pkPatientId,
  p.FirstName,
  p.Surname,
  ps.StatusCode,
  ps.StartDate,
  ps.EndDate
FROM 
  Patient p 
  INNER JOIN PatientStatus ps ON p.pkPatientId = ps.fkPatientId
WHERE
  /* the "2" is the maximum number of rows returned */
  2 > (
    SELECT 
      COUNT(*)
    FROM 
      Patient p_i 
      INNER JOIN PatientStatus ps_i ON p_i.pkPatientId = ps_i.fkPatientId
    WHERE
      ps_i.fkPatientId = ps.fkPatientId
      AND ps_i.StartDate < ps.StartDate
      /* switch between "<" and ">" to get the first/last rows */
  )
  AND EXISTS (
    SELECT   1 
    FROM     PatientStatus
    WHERE    fkPatientId = p.pkPatientId
    GROUP BY fkPatientId
    HAVING   COUNT(*) >= 2
  )
ORDER BY 
  ps.fkPatientId, 
  ps.StartDate

Примечание стороны: Для MySQL последний запрос мог бы быть единственной альтернативой - пока ПРЕДЕЛ не поддерживается в подзапросах.

Править: Я добавил условие, которое исключает пациентов только с одним PatientStatus запись. (Спасибо за подсказку, Ryan!)

Я не попробовал, но это могло работать;

SELECT /*(your select columns here)*/, row_number() over(ORDER BY ps.fkPatientId, ps.StartDate) as rownumber FROM Patient p INNER JOIN PatientStatus ps ON p.pkPatientId = ps.fkPatientId
where rownumber between 1 and 2

если это не работало, см. эту ссылку.

Добавление этого оператора Where к внешнему запросу первого решения Tomalak предотвратит Пациентов меньше чем с 2 записями состояния от того, чтобы быть возвращенным. Вы можете также "и" это в операторе Where второго запроса для тех же результатов.

WHERE pkPatientId IN (
    SELECT pkPatientID 
    FROM Patient JOIN PatientStatus ON pkPatientId = fkPatientId
    GROUP BY pkPatientID HAVING Count(*) >= 2
)

Проверьте, поддерживает ли Ваш сервер оконные функции:

SELECT * 
FROM Patient p
LEFT JOIN PatientStatus ps ON p.pkPatientId = ps.fkPatientId
QUALIFY ROW_NUMBER() OVER (PARTITION BY ps.fkPatientId ORDER BY ps.StartDate) < 3

Другая возможность, которая должна работать с SQL Server 2005:

SELECT * FROM Patient p
LEFT JOIN ( 
    SELECT *, ROW_NUMBER(PARTITION BY fsPatientId ORDER by StartDate) rn
    FROM PatientStatus) ps
ON p.pkPatientId = ps.fkPatientID 
and ps.rn < 3

Вот то, как я приблизился бы к этому:

-- Patients with at least 2 status records
with PatientsWithEnoughRecords as (
    select fkPatientId
        from PatientStatus as ps
        group by 
            fkPatientId
        having
            count(*) >= 2
)
select top 2 *
    from PatientsWithEnoughRecords as er 
        left join PatientStatus as ps on
            er.fkPatientId = ps.fkPatientId
    order by StartDate asc

Я не уверен, что определяет "первые" две записи состояния в Вашем случае, таким образом, я предположил, что Вы хотите самые ранние два StartDate** s. Измените последнее **order by пункт для получения записей, которыми Вы интересуетесь.

Править: SQL Server 2000 не поддерживает CTEs, таким образом, это решение будет действительно только работать непосредственно над 2005 и позже.

Ужасный, но этот не полагается на уникальность StartDate и работ над SQL 2000

select * 
from Patient p 
join PatientStatus ps on p.pkPatientId=ps.fkPatientId
where pkPatientStatusId in (
 select top 2 pkPatientStatusId 
 from PatientStatus 
 where fkPatientId=ps.fkPatientId 
 order by StartDate
) and pkPatientId in (
 select fkPatientId
 from PatientStatus
 group by fkPatientId
 having count(*)>=2
)