本文介绍了Order and select子句的完全Group BY错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

I've a temp table which results this -

number   contact_id      email           email_id
 8       204         ask@gmail.com           185
 8       205         ask@gmail.com           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子句以将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)

它给出一个错误--

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

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

例如

8       204         ask@gmail.com           185`

8       205         ask@gmail.com           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错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-01 03:19
查看更多