在 SQL Server 2008 中按用户名选择最新的记录组 [英] Select newest record group by username in SQL Server 2008

问题描述

我有一个包含这些数据的表格:

I have a table with this data:

ID   voting_ID   username   timestamp              XMLBallot
1    9           voter01    23. 4. 2012 8:54:45    xmldata
2    9           voter01    21. 4. 2012 14:00:34   xmldata
3    9           voter02    20. 4. 2012 16:01:10   xmldata
4    11          voter01    23. 4. 2012 8:40:45    xmldata
5    9           voter03    19. 4. 2012 21:18:49   xmldata

我只需要为特定 voting_ID 中的每个选民(用户名)获得一张最新的选票.

I need to get only one newest ballot for each voter (username) in specific voting_ID.

例如，我需要为 @voting_ID=9

ID   voting_ID   username   timestamp              XMLBallot
1    9           voter01    23. 4. 2012 8:54:45    xmldata
3    9           voter02    20. 4. 2012 16:01:10   xmldata
5    9           voter03    19. 4. 2012 21:18:49   xmldata

请帮我构建那个 SQL Server 2008 查询..谢谢PS:表名是ballots

Please help me build that SQL Server 2008 query.. thank you PS: table name is ballots

推荐答案

您在这里有几个选项，但使用添加一个 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

这篇关于在 SQL Server 2008 中按用户名选择最新的记录组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！