java 与 Mysql 5.7 不兼容（ORDER BY 子句的表达式 #1 不在 SELECT 列表中）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41465332/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Incompatibility with Mysql 5.7(Expression #1 of ORDER BY clause is not in SELECT list)
提问by Suriya Kumar
When I Executes the follwoing query i have received the Exception
当我执行以下查询时，我收到了异常
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
错误代码：3065 ORDER BY 子句的表达式 #1 不在 SELECT 列表中，引用不在 SELECT 列表中的列“webstore.level_depth”；这与 DISTINCT 不兼容
My Query
我的查询
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..??
我不明白为什么会这样......
回答by Suriya Kumar
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
我已经找到了我的问题的答案。实际上mysql 5.7在 sql 模式下包含“ ONLY_FULL_GROUP_BY”。所以我们不能在不在选择列表中的元素中执行 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'
into
进入
'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'
回答by Pradeep Kumar Prabaharan
ORDER BYcolumn should be column listed in the SELECTlist
ORDER BY列应该是列表中列出的SELECT列
Add c.level_depthin your select list
添加c.level_depth到您的选择列表中
Try:
尝试：
SELECT DISTINCT c.id_parent, c.id_category, cl.name, cl.description, cl.link_rewrite, c.level_depth
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 c.`level_depth` ASC, cl.`name` ASC;
回答by Jayesh Mulwani
Sql Feature Order by is as the name suggests used to order the Selected Columns on the basis of the Column mentioned in the below Syntax : Order by Column_Name ASC/DESC
Sql Feature Order by 顾名思义，用于根据以下语法中提到的 Column 对 Selected Columns 进行排序：Order by Column_Name ASC/DESC
So if you don't add the column using which you have decided to retrieve order set of data in the select clause you will get this error.
因此，如果您不添加决定在 select 子句中检索订单数据集的列，您将收到此错误。
回答by Heinan Cabouly
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)
ORDER BY c.`level_depth` ASC, cl.`name` ASC
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));
To summarize, you need to have the ORDER BYin the context of the SELECTcommand, in which case, with the WHERE, FROMand INNER JOIN.
总而言之，您需要ORDER BY在SELECT命令的上下文中使用，在这种情况下，使用WHERE,FROM和INNER JOIN。
