更新时间:2022-11-25 10:04:48
您在这里有几个选项,但使用添加一个 ROW_NUMBER
按 user
分组并排序(降序)于您的 时间戳
可让您轻松选择最新记录.
You have several options here but using adding a ROW_NUMBER
grouped by user
and sorted (descending) on your timestamp
allows you to easily select the latest records.
使用 ROW_NUMBER
SELECT *
FROM (
SELECT ID, voting_ID, username, timestamp, XMLBallot
, rn = ROW_NUMBER() OVER (PARTITION BY voting_ID, username ORDER BY timestamp DESC)
FROM Ballots
) bt
WHERE rn = 1
或者,您可以选择每个用户的最大时间戳并加入.
Alternatively, you can select the maximum timestamp per user and join on that.
使用 MAX
SELECT bt.ID, bt.voting_ID, bt.username, bt.timestamp, bt.XMLBallot
FROM Ballots bt
INNER JOIN (
SELECT username, voting_ID, timestamp = MAX(timestamp)
FROM Ballots
GROUP BY
username, voting_ID
) btm ON btm.username = bt.Username
AND btm.voting_ID = bt.voting_ID
AND btm.timestamp = bt.timestamp