MySQL 5.7 ORDER BY clause is not in SELECT

  • H5W3
  • 2021-03-15 12:31:55

当我执行以下查询时，我收到了异常

Error Code: 3065 Expression #1 of ORDER BY clause is not in SELECT

list, references column 'webstore.level_depth' which is not in

SELECT list; this is incompatible with DISTINCT

我的查询

     SELECT DISTINCT c.id_parent, c.id_category, cl.name, cl.description, cl.link_rewrite
FROM `pj_category_shop` cs, `pj_category` c
INNER JOIN `pj_category_lang` cl ON (c.`id_category` = cl.`id_category` AND cl.`id_lang` = 1 AND cl.id_shop = 2 )
WHERE (c.`active` = 1 OR c.`id_category` = 2)
AND cs.`id_category` = c.`id_category` AND cs.`id_shop` = 2
AND c.`id_category` != 1
AND `level_depth` <= 2
AND c.id_category IN (SELECT id_category FROM `pj_category_group` WHERE `id_group` IN (3))
ORDER BY `level_depth` ASC, cl.`name` ASC;

我不明白为什么会这样。

最佳答案

我已经找到了问题的答案。实际上mysql 5.7在sql模式下包含' ONLY_FULL_GROUP_BY '，因此我们无法在选择列表中未包含的元素中执行orderby" title="orderby">orderby。我们必须将其更改为

'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

进入

'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

我们可以通过执行以下查询来完成此操作

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

以上是 MySQL 5.7 ORDER BY clause is not in SELECT 的全部内容， 来源链接： www.h5w3.com/122165.html

回到顶部