当包含某些列时，MySQL ORDER BY AVG（）DESC不起作用 [英] MySQL ORDER BY AVG() DESC not working when certain columns are included

问题描述

我正在做一个查询来返回table1中的所有行，以及来自table2的平均评分：

  SELECT `table1`.`description`，AVG（`table2`.`rating`）AS avg_rating 
 FROM`table1` LEFT JOIN`table2` ON（`table2`.`botid` =`table1`.`id`` ）
 GROUP BY`table1`.`id` 
 ORDER BY avg_rating DESC 
  

问题是即使我指定 DESC ，结果也会返回 ASC ：

  + ------------- + ------------ + 
 |描述| avg_rating | 
 + ------------- + ------------ + 
 | test2 | 1.0000 | 
 | test3 | 3.0000 | 
 | test4 | 3.0000 | 
 | saasdf | 4.0000 | 
 + ------------- + ------------ + 
  

为什么MySQL没有遵守 ORDER BY ... DESC ？

即使是更加狡猾，当我从要检索的列列表中移除 table1.description 时，它可以正常工作：

  SELECT AVG（`table2`.`rating`）AS avg_rating 
 FROM`table1` LEFT JOIN`table2` ON（`table2`.`botid` =`table1`。 `id`）
 GROUP BY`table1`.`id` 
 ORDER BY avg_rating DESC 
  

返回：

  + ------------ + 
 | avg_rating | 
 + ------------ + 
 | 4.0000 | 
 | 3.0000 | 
 | 3.0000 | 
 | 1.0000 | 
 + ------------ + 
  

这是我的数据：

table1：

  id |描述
 -  + ----------- 
 6 | test2 
 16 | test3 
 54 | test4 
 72 | saasdf 
  

table2：

  botid |评分
 ----- + ------ 
 6 | 1 
 16 | 3 
 54 | 3 
 72 | 4 
  

（为了这个例子， table1中的记录之间存在一对一的关系和 table2 ，但实际上会存在一对多的关系。）

以及我的模式：

$ pre codeREATE TABLE`table1`（
`id` int（11）NOT NULL ，
`description` longtext NOT NULL
）ENGINE = MyISAM DEFAULT CHARSET = utf8;

CREATE TABLE`table2`（
`botid` int（11）NOT NULL，
`rating` int（11）NOT NULL
）ENGINE = InnoDB DEFAULT CHARSET = UTF8;

table1.id 和 table2.botid ，但这不应该影响结果。我使用MySQL 5.7.7-rc-log。

我有很多使用聚合函数，GROUP BY和ORDER BY的经验，但我从未遇到过任何喜欢这个。任何建议？

解决方案

请升级到GA版本（5.7.9是第一个; 5.7.18是存在的），然后测试再次。 IIRC，这个地区有一个bug。

如果错误持续存在，请提供命令来重现错误并将其提交到bugs.mysql.com。 p>

我强烈建议您从MyISAM更改为InnoDB。 Oracle可能会抛出错误报告，因为它涉及到MyISAM。

同时，您可以看到这是否给您正确的排序：

  SELECT`table1`.`description`，
（SELECT AVG（`rating`）
 FROM table2 
 where botid = table1。 id 
）AS avg_rating 
 FROM`table1` 
 ORDER BY avg_rating DESC 
  

为您的版本和我的版本提供 EXPLAIN FORMAT = JSON SELECT ... 。

Explanation

您的原始查询似乎存在问题 JOIN ... GROUP BY 。首先， JOIN 收集比您开始时更多的行，然后 GROUP BY 将其缩回原始数量。

我重写的是原始行数（在 table1 中），并探测表2以获取必要的内容。主要（在这种情况下）它避免了tmp表和文件。

I'm doing a query to return all the rows in table1, along with their average rating from table2:

SELECT `table1`.`description`, AVG( `table2`.`rating` ) AS avg_rating
FROM `table1` LEFT JOIN `table2` ON ( `table2`.`botid` = `table1`.`id` )
GROUP BY `table1`.`id`
ORDER BY avg_rating DESC

The problem is that even though I specify DESC, the results are being returned ASC:

+-------------+------------+
| description | avg_rating |
+-------------+------------+
| test2       |     1.0000 |
| test3       |     3.0000 |
| test4       |     3.0000 |
| saasdf      |     4.0000 |
+-------------+------------+

Why isn't MySQL honoring ORDER BY...DESC?

Even weirder, when I remove table1.description from the list of columns to retrieve, it works properly:

SELECT AVG( `table2`.`rating` ) AS avg_rating
FROM `table1` LEFT JOIN `table2` ON ( `table2`.`botid` = `table1`.`id` )
GROUP BY `table1`.`id`
ORDER BY avg_rating DESC

Returns:

+------------+
| avg_rating |
+------------+
|     4.0000 |
|     3.0000 |
|     3.0000 |
|     1.0000 |
+------------+

Here is my data:

table1:

id|description
--+-----------
 6|test2
16|test3
54|test4
72|saasdf

table2:

botid|rating
-----+------
    6|1
   16|3
   54|3
   72|4

(For the sake of this example there is a one-to-one relationship between the records in table1 and table2, but in reality there will be a one-to-many relationship.)

And my schema:

CREATE TABLE `table1` (
  `id` int(11) NOT NULL,
  `description` longtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `table2` (
  `botid` int(11) NOT NULL,
  `rating` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

There are indexes on both table1.id and table2.botid, although that shouldn't affect the results. I'm using MySQL 5.7.7-rc-log.

I have plenty of experience using aggregate functions, GROUP BY and ORDER BY but I've never come across anything like this. Any suggestions?

解决方案

Please upgrade to a GA version (5.7.9 was the first; 5.7.18 exists), then test again. IIRC, there was a bug somewhere in this area.

If the bug persists, provide the commands to reproduce the error and submit it to bugs.mysql.com .

I strongly recommend you change from MyISAM to InnoDB. Oracle may throw out the bug report since it involves MyISAM.

Meanwhile, you could see if this gives you the correct ordering:

SELECT  `table1`.`description`, 
        ( SELECT  AVG(`rating` )
            FROM  table2
            WHERE  botid = table1.id 
        ) AS avg_rating
    FROM  `table1`
    ORDER BY  avg_rating DESC 

Provide EXPLAIN FORMAT=JSON SELECT ... for both your version and my version.

Explanation

Your original query appears to have the "inflate-deflate" problem of JOIN ... GROUP BY. First the JOIN gathers more "rows" than you started with, then the GROUP BY shrinks it back to the original number.

My rewrite sticks to the original number of rows (in table1) and probes table 2 for the necessary stuff. Primarily (in this situation) it avoids the tmp table and filesort.

这篇关于当包含某些列时，MySQL ORDER BY AVG（）DESC不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！