与 Mysql 5.7 不兼容(ORDER BY 子句的表达式 #1 不在 SELECT 列表中) [英] Incompatibility with Mysql 5.7(Expression #1 of ORDER BY clause is not in SELECT list)
问题描述
当我执行以下查询时,我收到了异常
When I Executes the follwoing query i have received the Exception
错误代码:3065 ORDER BY 子句的表达式 #1 不在 SELECT 中列表,引用列 'webstore.level_depth' 不在选择列表;这与 DISTINCT 不兼容
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;
我不明白为什么会发生这种情况..??
I don't understand why this happening..??
推荐答案
我已经找到了问题的答案.实际上 mysql 5.7 在 sql 模式下包含 'ONLY_FULL_GROUP_BY'.所以我们不能在select列表之外的元素中执行orderby.我们必须将其从
I have find the answer for my question.Actually mysql 5.7 contains 'ONLY_FULL_GROUP_BY' in sql mode.So we can't perform orderby in the element that is not in select list.we have to change it from
'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'
我们可以通过执行以下查询来做到这一点
We can done this by executing the following queries
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 子句的表达式 #1 不在 SELECT 列表中)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!