我一直在弄清楚做GROUP BY
的语法,只显示每个用户(通过db_UserId
)的最新ups.db_LastUpdate
的行。
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)
谢谢你的帮助
使用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行作为结果。
如果你的表有一个唯一的ID列,我通常这样处理这种情况:
WITH LastEdit AS (
SELECT ups.db_Code, ups.db_Initials, ups.db_LastUpdate
FROM tblUserSel ups
WHERE ups.db_UserTech = 'U' AND ups.ID = (
SELECT TOP 1 ID
FROM tblUserSel upsn
WHERE ups.db_Initials = upsn.db_Initials
ORDER BY upsn.db_LastUpdate DESC
)
)
SELECT up.db_FirstName, up.db_LastName, up.db_UserId, le.db_Initials, le.db_LastUpdate
FROM tblUserProfile up
INNER JOIN LastEdit le
ON le.db_Code = up.db_UserId