在MySQL中选择DISTINCT和ORDER BY [英] SELECT DISTINCT and ORDER BY in MySQL

问题描述

似乎在MySQL 5.7版中，他们添加了一件令人讨厌的东西，该东西曾经是(或仍然是)真正的

It seems like in version 5.7 of MySQL, they added one nasty thing which was (or still is) a real headache for those who deal with SQL Server.

问题是:当您尝试对一组列的SELECT DISTINCT行并且想要ORDER BY另一组列时，MySQL引发错误.在以前的版本5.6中，甚至在版本5.7的某些内部版本中，您都可以执行此操作，但是现在禁止这样做(至少默认情况下).

The thing is: MySQL throws an error, when you try to SELECT DISTINCT rows for one set of columns and want to ORDER BY another set of columns. Previously, in version 5.6 and even in some builds of version 5.7 you could do this, but now it is prohibited (at least by default).

我希望存在一些配置，我们可以设置一些变量以使其起作用.但是不幸的是我不知道那个讨厌的变量.我希望有人知道.

I hope there exists some configuration, some variable that we could set to make it work. But unfortunately I do not know that nasty variable. I hope someone knows that.

编辑

在我的情况下，这是一些典型的查询，实际上已经工作了多年(直到最新的MySQL 5.7版本):

This is some typical query in my case that worked literally for years (until the last build of MySQL 5.7):

SELECT DISTINCT a.attr_one, a.attr_two, a.attr_three, b.attr_four FROM table_one a
LEFT JOIN table_two b ON b.some_idx = a.idx
ORDER BY b.id_order

而且，的确，如果我现在将b.id_order包括在SELECT部分中(如MySQL建议的那样)，那么我得到的将是垃圾.

And, indeed, if I now include b.id_order to the SELECT part (as MySQL suggests doing), then what I will get, will be rubbish.

推荐答案

在大多数情况下，DISTINCT子句可以视为GROUP BY的特例.例如，

In most cases, a DISTINCT clause can be considered as a special case of GROUP BY. For example,

ONLY_FULL_GROUP_BY

MySQL 5.7.5及更高版本实现对功能依赖性的检测.如果启用了 ONLY_FULL_GROUP_BY SQL模式(默认情况下)，则MySQL拒绝查询，其中选择列表，HAVING条件或ORDER BY列表引用未在GROUP BY中命名的未聚合列子句在功能上也不依赖于它们. (在5.7.5之前，MySQL不会检测到功能依赖性，并且默认情况下未启用ONLY_FULL_GROUP_BY.有关5.7.5之前的行为的说明，)

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior )

如果禁用了ONLY_FULL_GROUP_BY，则对标准SQL使用GROUP BY的MySQL扩展允许选择列表，HAVING条件或ORDER BY列表引用未聚合的列，即使这些列在功能上不依赖于GROUP BY列也是如此.这使MySQL接受前面的查询.在这种情况下，服务器可以从每个组中自由选择任何值，因此，除非它们相同，否则选择的值是不确定的，这可能不是您想要的.此外，通过添加ORDER BY子句不能影响从每个组中选择值.选择值之后，将进行结果集排序，并且ORDER BY不会影响服务器在每个组中选择哪个值.禁用ONLY_FULL_GROUP_BY很有用，主要是因为您知道由于数据的某些属性，每个未聚合列中未在GROUP BY中命名的所有值对于每个组都是相同的.

If ONLY_FULL_GROUP_BY is disabled, a MySQL extension to the standard SQL use of GROUP BY permits the select list, HAVING condition, or ORDER BY list to refer to nonaggregated columns even if the columns are not functionally dependent on GROUP BY columns. This causes MySQL to accept the preceding query. In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate, which is probably not what you want. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which value within each group the server chooses. Disabling ONLY_FULL_GROUP_BY is useful primarily when you know that, due to some property of the data, all values in each nonaggregated column not named in the GROUP BY are the same for each group.

了解更多信息 http://dev.mysql. com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

特定答案

SELECT DISTINCT attr_one,
            attr_two,
            attr_three,
            attr_four
FROM
  (SELECT a.attr_one,
      a.attr_two,
      a.attr_three,
      b.attr_four
   FROM table_one a
   LEFT JOIN table_two b ON b.some_idx = a.idx
   ORDER BY b.id_order) tmp

这篇关于在MySQL中选择DISTINCT和ORDER BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！