sql - 聚合查询中的非分组列

Question

假设我有一个包含三列的表：c1、c2和c3。我想在每个c1中获取最大值c2和c3包含最大值的行的值。

这是一个例子。SayMyTable包含以下记录：

c1   c2   c3
---  ---  ---
1    32   12
1    27   15
1    12   21
2    23   33
2    36   49
3    48   17
3    21   50
3    7    25

然后我的查询应该返回

c1   MAX(c2)  c3
---  -------  ---
1    32       12
2    36       49
3    48       17

但是当我查询

SELECT c1, MAX(c2), c3 FROM MyTable GROUP BY c1

我得到错误：

Column 'MyTable.c3' is invalid in the select list because it is not 
contained in either an aggregate function or the GROUP BY clause.

如何修复此查询？我尝试过子查询、连接等，但均无济于事。

标签： sqlsql-server

Answer 1

我认为相关子查询where是一个简单的解决方案：

select t.*
from t
where t.col2 = (select max(t2.col2) from t t2 where t2.col1 = t.col1);

这通常具有非常好的性能特征。更传统的解决方案使用row_number(：

select t.*
from (select t.*,
             row_number() over (partition by col1 order by col2 desc) as seqnum
      from t
     ) t
where seqnum = 1;

如果你有一个单独的 col1 值的单独表，那么apply也可以有很好的性能：

select t.*
from table_col1 c1 cross apply
     (select top (1) t.*
      from t 
      where t.col1 = c1.col1
      order by t.col2 desc
     ) t;