mysql - 聚合后如何保留额外的列？

Question

我在 mysql 中有下表，它是实际问题的简化版本。

+-------+-------+-------+-------+
| a_col | b_col | c_col | extra |
+-------+-------+-------+-------+
|     1 |     1 |     1 |     7 |*
|     1 |     2 |     1 |    10 |
|     1 |     2 |     2 |    20 |*
|     1 |     3 |     2 |    20 |
|     1 |     3 |     3 |    15 |*
+-------+-------+-------+-------+

我想选择标有*的行，得到下表:

+-------+-------+-------+-------+
| a_col | b_col | c_col | extra |
+-------+-------+-------+-------+
|     1 |     1 |     1 |     7 |
|     1 |     2 |     2 |    20 |
|     1 |     3 |     3 |    15 |
+-------+-------+-------+-------+

如果两行a_col和b_col的值相同，则保留c_col中值最大的一行

我的尝试是:

select a_col, b_col, max(c_col), extra
from mytable
group by a_col, b_col

但我收到以下错误消息:

ERROR 1055 (42000): Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'bd17_g12.mytable.extra' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

在不提及“额外”列的情况下，我得到了接近我想要的东西:

+-------+-------+------------+
| a_col | b_col | max(c_col) |
+-------+-------+------------+
|     1 |     1 |          1 |
|     1 |     2 |          2 |
|     1 |     3 |          3 |
+-------+-------+------------+

但我需要保留“额外”列的值

Answer 1

您可以通过应用聚合group_concat函数来获取extra和c_col列。要选择相关的最高 c_col 及其相关的额外值，您可以在其上使用 substring_index

select a_col, b_col, max(c_col), 
substring_index(group_concat(extra order by c_col desc),',',1) `extra`
from mytable
group by a_col, b_col

DEMO

或者使用自连接

select a.*
from mytable a
left join mytable b 
          on a.a_col = b.a_col 
          and a.b_col = b.b_col
          and a.c_col < b.c_col
where b.c_col is null

DEMO