Order and select子句的完全Group BY错误 [英] Full Group BY error in order and select clause

问题描述

我有一个临时表，其结果是-

I've a temp table which results this -

number   contact_id      email           email_id
 8       204         [e-mail]           185
 8       205         [e-mail]           186

我需要获取按email列对查询进行分组的结果.

I need to fetch the result grouping the query with email column.

当我尝试这个时-

SELECT number, contact_id, email_id 
        FROM       contact 
       GROUP BY email
      ORDER BY   contact_id, email_id

它给出FULL_GROUP_BY错误

SELECT列表不在GROUP BY子句中，并且包含未聚合的列...

SELECT list is not in GROUP BY clause and contains nonaggregated column...

当我修改select子句以将ANY_VALUE关键字用作

When I modify the select clause to use ANY_VALUE keyword as

SELECT ANY_VALUE(number), ANY_VALUE(contact_id), ANY_VALUE(email_id)

它给出一个错误--

ORDER BY子句的表达式#1不在GROUP BY子句中，并且包含非聚合列'contact_id'，该列在功能上不依赖于GROUP BY子句中的列；这与sql_mode = only_full_group_by

Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'contact_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

有什么办法可以将结果按email分组并仅返回一行?

Is there any way I could group the result by email and return only one row ?

例如

8       204         [e-mail]           185`

或

8       205         [e-mail]           186

注意:我需要在启用only_full_group_by模式(MySQL 5.7+)的情况下执行此操作.我也不能删除任何order_by子句.

Note: I need to do this keeping only_full_group_by mode enabled (MySQL 5.7+). Also I can't remove any order_by clause.

推荐答案

我已设法通过以下查询解决此问题.如果有人有更好的方法，请发布答案.

I've managed to fix this with below query. If anyone has a better way, please post as an answer.

   SELECT ANY_VALUE(number), ANY_VALUE(contact_id), ANY_VALUE(email_id)
    FROM       contact 
   GROUP BY email
   ORDER BY   MIN(contact_id), MIN(email_id)

这篇关于Order and select子句的完全Group BY错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！