分组依据和Mysql 5.7 -错误的分组

Question

我的主机把mysql的版本从5.6改成了5.7 (禁用了only_full_group_by)，我的查询出现了问题。

SELECT  `id`, `category`, `name`, `number`, `lang`
FROM `test`
WHERE `category` = 'Cat1'
ORDER BY FIELD(lang, 'EN', 'JP')

如下所示显示了良好的结果：

1 Cat1 Test1 23 EN
2 Cat1 Test2 21 EN
2 Cat1 Test1 23 JP
4 Cat1 Test1 23 JP
5 Cat1 Test2 21 JP

然后我对结果进行分组，只得到一个结果，如下所示：

SELECT *
FROM
(
SELECT  `id`, `category`, `name`, `number`, `lang`
FROM `test`
WHERE `category` = 'Cat1'
ORDER BY FIELD(lang, 'EN', 'JP')
) as table
GROUP BY number
ORDER BY number DESC

在MySQL5.6中，它起作用了。在5.7中不是。

5.6的结果：

1 Cat1 Test2 21 EN
2 Cat1 Test1 23 EN

5.7的结果：

1 Cat1 Test2 21 JP
2 Cat1 Test1 23 EN

为什么MySQL5.7中的GROUP BY不取第一个元素，而是随机取一个？

Answer 1

使用不带聚合函数的GROUP BY可以随心所欲地选取值。MySQL不能保证这一点。

SELECT 
T.*
FROM
`test` T
INNER JOIN
(
    SELECT  
    MIN(`id`) min_id, 
    `category`,
    `number`
    FROM `test`
    WHERE `category` = 'Cat1'
    GROUP BY `number`
) as t
ON T.`id` = t.min_id AND T.category = t.category AND t.`number` = T.`number`
ORDER BY T.number DESC;

编辑：

为了让类别Cat1下的每个数字都有一行，优先级最高的是lang='EN'：

SELECT 
*
FROM 
(
    SELECT 
    *,
    IF(@sameNumber = `number`, @rn := @rn + 1 ,
       IF(@sameNumber := `number`, @rn := 1, @rn := 1)
       ) AS groupWiseRankNumber
    FROM test 
    CROSS JOIN (SELECT @sameNumber := NULL , @rn := 1) var
    WHERE category = 'Cat1'
    ORDER BY `number` , FIELD(lang, 'EN','JP')
) AS t
WHERE t.groupWiseRankNumber <= 1;

Answer 2

您只是观察到MySQL文档一直在说的话：group by查询的select中的非聚合列来自不确定的行。无论如何，您都不应该使用此语法。它是一个MySQL (Mis)特性，只会导致更多的问题而不是解决问题，并且它在任何其他非MySQL派生的数据库中都不可用。

这里有一个方法来做你想做的事情，它应该更有效率：

select t.*
from test t
where number = 21 and lang = 'EN'
union all
select t.*
from test t
where number = 21 and lang = 'JP' and
      not exists (select 1 from test t2 where t2.number = t.number and t2.lang = 'EN');

这可以利用test(number, lang)上的索引来实现最佳性能。

Answer 3

在mysql 5.7中，如果所有的ORDER BY都站在GROUP BY之前的子查询中，它们似乎都会被忽略。

我在我的查询中也有同样的问题。我找到了变通的办法。支持按键列添加ORDER by。如果id是表中的主键，则可以添加

GROUP BY `id`

在此之前

ORDER BY FIELD(lang, 'EN', 'JP')

查询：

`SELECT *
FROM
(
  SELECT  `id`, `category`, `name`, `number`, `lang`
  FROM `test`
  WHERE `category` = 'Cat1'
  GROUP BY `id`
  ORDER BY FIELD(lang, 'EN', 'JP')
) as table
GROUP BY number
ORDER BY number DESC`