问题描述
我有一个临时表,其结果是-
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错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!