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

问题描述：

我在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 | 
+-------+-------+------------+

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

答

你可以得到extra和c_col列应用聚合group_concat函数。并挑选相关的最高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

答

据我了解，你想extra最大的c_col。

SQL Fiddle

的MySQL 5.6架构设置：

查询1：

select t2.* 
from (
    select a_col, b_col, max(c_col) c_colm 
    from t 
    group by a_col, b_col 
) t1 
    INNER join t t2 
    on t1.a_col = t2.a_col 
     and t1.b_col = t2.b_col 
     and t1.c_colm = t2.c_col

Results：

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

答

包含extra列时出现的错误似乎不适用于低版本的mysql（如5.5版）。所以问题可能是针对高版本的mysql的'ONLY_FULL_GROUP_BY'问题，如这里讨论的：Error Code: 1055 incompatible with sql_mode=only_full_group_by

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

相关推荐