SQL查询-联接返回联接表的前两个记录

问题描述：

我有两个表:

患者

pkPatientId
名字
姓氏

PatientStatus

pkPatientStatusId
fkPatientId
StatusCode
开始日期
EndDate

患者-> PatientStatus 是一对多关系.

Patient -> PatientStatus is a one to many relationship.

我想知道是否有可能在SQL中进行联接，该联接仅返回每个Patient的前两个PatientStatus记录.如果仅存在一个PatientStatus记录，则不应在结果中返回该记录.

I am wondering if its possible in SQL to do a join which returns only the first two PatientStatus records for each Patient. If only one PatientStatus record exists then this should not be returned in the results.

我查询的普通联接是:

SELECT FROM Patient p INNER JOIN PatientStatus ps ON p.pkPatientId = ps.fkPatientId
ORDER BY ps.fkPatientId, ps.StartDate

答

如果您使用的是SQL Server 2005或更高版本，那么CTE可能是最好的选择，但是如果您希望与其他平台更兼容一些，这应该是最好的选择.工作:

A CTE is probably your best bet if you're in SQL Server 2005 or greater, but if you want something a little more compatible with other platforms, this should work:

SELECT
     P.pkPatientID,
     P.FirstName,
     P.LastName,
     PS1.StatusCode AS FirstStatusCode,
     PS1.StartDate AS FirstStatusStartDate,
     PS1.EndDate AS FirstStatusEndDate,
     PS2.StatusCode AS SecondStatusCode,
     PS2.StartDate AS SecondStatusStartDate,
     PS2.EndDate AS SecondStatusEndDate
FROM
     Patient P
INNER JOIN PatientStatus PS1 ON
     PS1.fkPatientID = P.pkPatientID
INNER JOIN PatientStatus PS2 ON
     PS2.fkPatientID = P.pkPatientID AND
     PS2.StartDate > PS1.StartDate
LEFT OUTER JOIN PatientStatus PS3 ON
     PS3.fkPatientID = P.pkPatientID AND
     PS3.StartDate < PS1.StartDate
LEFT OUTER JOIN PatientStatus PS4 ON
     PS4.fkPatientID = P.pkPatientID AND
     PS4.StartDate > PS1.StartDate AND
     PS4.StartDate < PS2.StartDate
WHERE
     PS3.pkPatientStatusID IS NULL AND
     PS4.pkPatientStatusID IS NULL

对于我来说，您想要的是前两个状态而不是后两个状态，这似乎有些奇怪，但是我假设您知道自己想要什么.

It does seem a little odd to me that you would want the first two statuses instead of the last two, but I'll assume that you know what you want.

如果您获得更好的性能，也可以使用WHERE NOT EXISTS代替PS3和PS4连接.

You can also use WHERE NOT EXISTS instead of the PS3 and PS4 joins if you get better performance with that.

SQL查询-联接返回联接表的前两个记录

相关推荐