代码之家 › 专栏 › 技术社区 › Dan Herbert

获取SQL Server中某列的最大值的行

sql-server-2005 sql-server sql

Dan Herbert · 技术社区 · 16 年前

我发现了一个和这个问题非常相似的问题,但是使用的功能似乎是Oracle独有的。我希望在SQL Server中执行此操作。

我有一张这样的桌子:

MyTable
--------------------
MyTableID  INT  PK
UserID     INT
Counter    INT

每个用户可以有多行,具有不同的 Counter 在每一行。我需要找到最高的一行 计数器 每个用户的值。

如何在SQL Server 2005中执行此操作?

我能想到的最好的方法是查询返回 MAX(Counter) 对于每一个 UserID 但我需要整行,因为为了简单起见,表定义中没有显示此表中的其他数据。

编辑: 这篇文章中的一些答案引起了我的注意,我忘记了一个重要的细节。可以有2+行,其中一个用户ID可以有相同的最大计数器值。下面的例子更新了预期的数据/输出应该是什么。

有了这些数据:

MyTableID   UserID   Counter
---------   -------  --------
1           1         4
2           1         7
3           4         3
4           11        9
5           11        3
6           4         6
...
9           11        9

对于重复的max值,我希望得到这些结果,请按照SQL Server选择它们的顺序选择第一个结果。在这种情况下,返回哪些行并不重要,只要用户ID/计数器对是不同的:

MyTableID   UserID    Counter
---------   -------   --------
2           1         7
4           11        9
6           4         6

5 回复 | 直到 14 年前

marc_s MisterSmith 16 年前

对于这种情况,我喜欢使用一个通用的表表达式,其中包含一个合适的row_number()函数:

WITH MaxPerUser AS
(
  SELECT 
    MyTableID, UserID, Counter,
    ROW_NUMBER() OVER(PARTITION BY userid ORDER BY Counter DESC) AS 'RowNumber'
  FROM dbo.MyTable
)
SELECT MyTableID, UserID, Counter 
FROM MaxPerUser
WHERE RowNumber = 1

它将数据分区到用户ID上,为每个用户按计数器(降序)排序,然后为每个用户标记从1开始的行。只选择行数为1的行,并且每个用户都有最大值。

这很简单:-)我得到的结果如下:

MyTableID   UserID  Counter  
   2           1        7   
   6           4        6
   4          11        9

每个用户只有一个条目,不管每个用户有多少行恰好具有相同的最大值。

Leigh josh poley 14 年前

我想这对你有帮助。

SELECT distinct(a.userid), MAX(a.counterid) as counterid 
FROM    mytable a INNER JOIN mytable b ON a.mytableid = b.mytableid 
GROUP BY a.userid

SQLMenace 16 年前

有几种方法可以做到这一点,看看这个 Including an Aggregated Column's Related Values 显示了几种方法,包括性能差异

这里有一个例子

select t1.*
from(
select UserID, max(counter) as MaxCount
from MyTable
group by UserID) t2
join MyTable t1 on t2.UserID =t1.UserID
and t1.counter = t2.counter

anthonyv 16 年前

试试这个…我非常肯定这是唯一一种真正确保每个用户都能得到一行的方法。

SELECT MT.* 
FROM    MyTable MT
    INNER JOIN (
        SELECT  MAX(MID.MyTableId) AS MaxMyTableId,
            MID.UserId
        FROM    MyTable MID
            INNER JOIN (
                SELECT  MAX(Counter) AS MaxCounter, UserId
                FROM    MyTable
                GROUP BY UserId
            ) AS MC
                ON (MID.UserId = MC.UserId
                    AND MID.Counter = MC.MaxCounter)
        GROUP BY MID.UserId
    ) AS MID
        ON (MID.UserId = MC.UserId
            AND MID.MyTableId = MC.MaxMyTableId)

D'Arcy Rittich 16 年前

select m.* 
from MyTable m
inner join (
    select UserID, max(Counter) as MaxCounter
    from MyTable
    group by UserID
) mm on m.UserID = mm.UserID and m.Counter = mm.MaxCounter