sql - 使用 GROUP BY 只显示每个用户最新更新的行

Question

我一直无法弄清楚执行 GROUP BY 的语法，以便只显示每个用户具有最新 ups.db_LastUpdate 的行(通过 db_UserId).

SELECT up.db_FirstName, up.db_LastName, up.db_UserId, ups.db_Initials, ups.db_LastUpdate 
FROM tblUserProfile up
    JOIN tblUserSel ups
        ON ups.db_Code = up.db_UserId
WHERE ups.db_UserTech = 'U'

输出(会有几百个用户，但你明白了):

Jeff | Ledger | 1-34 | JL | 2015-08-11
Jeff | Ledger | 1-34 | DBC | 2015-06-06
Jeff | Ledger | 1-34 | YX | 2015-08-01
John | Barker | 1-26 | JR | 2015-04-04
John | Barker | 1-26 | YY | 2015-02-18
John | Barker | 1-26 | FF | 2015-11-14

可能类似于 GROUP BY ups.dbUserId, MAX(db_LastUpdate)

谢谢你的帮助

Answer 1

使用ROW_NUMBER:

;WITH CTE AS
(
    SELECT  up.db_FirstName, 
            up.db_LastName, 
            up.db_UserId, 
            ups.db_Initials, 
            ups.db_LastUpdate,
            RN = ROW_NUMBER() OVER(PARTITION BY up.db_UserId ORDER BY ups.db_LastUpdate DESC)
    FROM tblUserProfile up
    INNER JOIN tblUserSel ups
            ON ups.db_Code = up.db_UserId
    WHERE ups.db_UserTech = 'U'
)
SELECT *
FROM CTE
WHERE RN = 1;

正如评论中所指出的，您可以使用 MAX 然后加入您的表:

;WITH CTE AS
(
    SELECT  up.db_UserId, 
            MAX(ups.db_LastUpdate) MaxLastUpdate
    FROM tblUserProfile up
    INNER JOIN tblUserSel ups
            ON ups.db_Code = up.db_UserId
    WHERE ups.db_UserTech = 'U'
    GROUP BY up.db_UserId
)
SELECT B.*
FROM CTE A
INNER JOIN tblUserSel B
    ON A.db_UserId = B.db_Code
    AND A.MaxLastUpdate = B.db_LastUpdate;

但是您需要知道，如果对于同一用户存在具有相同日期的行，您将得到这 2 行作为结果。