为现有查询选择最大值

Question

首先，这是我的代码

SELECT dbo.tblPat.pID, dbo.tblRec.rID,
right(rPCheck, LEN(rPCheck) - (CHARINDEX('|', rPCheck))) as pCheck,
right(rGP, LEN(rGP) - (CHARINDEX('|', rGP))) as GPCheck
FROM
dbo.tblPat INNER JOIN
dbo.tblPatRecords ON dbo.tblPat.pID = dbo.tblPatRecords.pID INNER JOIN
dbo.tblRec ON dbo.tblPatRecords.rID = dbo.tblRec.rID
WHERE     
(DATEPART(dw, dbo.tblRec.rDateRecAdd) IN (5, 6, 7)) 
AND (dbo.tblRec.rGP <> N'0') 
AND (dbo.tblRec.rPCheck <> N'0')
AND right(rPCheck, LEN(rPCheck) - (CHARINDEX('|', rPCheck))) > right(rGP, LEN(rGP) - (CHARINDEX('|', rGP)))
ORDER BY dbo.tblPat.pID, dbo.tblRec.rID

，基本上以这种格式返回数据

39  366 26/06/2008 16:54:35 26/06/2008 15:04:53
39  368 27/06/2008 09:33:55 27/06/2008 08:57:07
39  369 27/06/2008 09:35:14 27/06/2008 08:57:07
39  370 27/06/2008 09:36:34 27/06/2008 08:57:07
39  371 27/06/2008 09:37:33 27/06/2008 08:57:07
39  372 27/06/2008 09:37:33 27/06/2008 08:57:07
39  373 27/06/2008 09:37:33 27/06/2008 08:57:07
39  374 27/06/2008 09:37:33 27/06/2008 08:57:07
*39 397 27/06/2008 13:13:49 27/06/2008 12:48:25*
92  46310   19/08/2008 15:52:50 03/10/2008 14:50:00
92  46313   19/08/2008 15:52:50 03/10/2008 14:52:50
92  46315   19/08/2008 15:52:50 03/10/2008 14:52:50
92  46558   19/08/2008 15:52:50 03/10/2008 19:26:04
92  46559   19/08/2008 15:52:50 03/10/2008 19:26:04
92  46623   04/10/2008 10:46:39 03/10/2008 19:26:04
*92 46632   04/10/2008 10:46:39 03/10/2008 19:26:04*

正如您可能已经猜到的那样，我需要为每个 PID 选择最后一个条目，所以在这种情况下，我需要

39  397 27/06/2008 13:13:49 27/06/2008 12:48:25
92  46632   04/10/2008 10:46:39 03/10/2008 19:26:04

任何帮助都会很棒。我可以作弊并将其变成一个视图，然后再做另一个视图，但我想尽可能干净

Answer 1

不要直接 JOIN 到 tblPatRecords，而是尝试用子查询的联接替换，例如将 FROM 子句替换为：

FROM dbo.tblPat INNER JOIN
(
    SELECT pID, MAX(rID) AS rID
    FROM dbo.tblPatRecords
    GROUP BY pID
) t ON dbo.tblPat.pID = t.pID
INNER JOIN dbo.tblRec ON t.rID = dbo.tblRec.rID

Answer 2

如果您使用的是 SQL Server 2005 或 2008，则可以使用 ROW_NUMBER 函数。试试这个：

WITH Results AS (
SELECT  dbo.tblPat.pID, dbo.tblRec.rID,
        RIGHT(rPCheck, LEN(rPCheck) - (CHARINDEX('|', rPCheck))) as pCheck,
        RIGHT(rGP, LEN(rGP) - (CHARINDEX('|', rGP))) as GPCheck,
        ROW_NUMBER() OVER(PARTITION BY dbo.tblPat.pID ORDER BY dbo.tblRec.rID DESC) Id
FROM dbo.tblPat 
INNER JOIN dbo.tblPatRecords 
ON dbo.tblPat.pID = dbo.tblPatRecords.pID 
INNER JOIN dbo.tblRec 
ON dbo.tblPatRecords.rID = dbo.tblRec.rID
WHERE (DATEPART(dw, dbo.tblRec.rDateRecAdd) IN (5, 6, 7)) 
AND (dbo.tblRec.rGP <> N'0') 
AND (dbo.tblRec.rPCheck <> N'0')
AND right(rPCheck, LEN(rPCheck) - (CHARINDEX('|', rPCheck))) > right(rGP, LEN(rGP) - (CHARINDEX('|', rGP)))
)

SELECT *
FROM Results
WHERE Id = 1