MySQL order 子句中的未知列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17312297/
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
Unknown column in order clause
提问by Jess McKenzie
#1054 - Unknown column 'default_ps_products.manufacturer_id' in 'order clause'
#1054 - Unknown column 'default_ps_products.manufacturer_id' in 'order clause'
Why am I getting the above error with the statement below it works fine without the pin the statement and I am not using an order clause?
为什么我在下面的语句中出现上述错误，它p在没有in语句的情况下工作正常，而且我没有使用 order 子句？
SELECT * FROM `default_ps_products` p WHERE p.`manufacturer_id` = 2
采纳答案by Joachim Isaksson
Since you posted a partial query this wasn't obvious from the start, but your full querymakes it clear;
由于您发布了部分查询，因此从一开始这并不明显，但是您的完整查询已经清楚了；
SELECT *
FROM default_ps_products
WHERE manufacturer_id=2
ORDER BY `default_ps_products`.`manufacturer_id` ASC
LIMIT 0, 30
When you add an alias to default_ps_productstable in the select, you can't selectively use the alias only in the WHEREclause, you'll also need to change the ORDER BYto use the same alias. The full query should in other words be;
当您default_ps_products在 select 中为table添加别名时，您不能选择性地仅在WHERE子句中使用别名，您还需要更改 theORDER BY以使用相同的别名。换句话说，完整的查询应该是；
SELECT *
FROM default_ps_products p
WHERE p.manufacturer_id=2
ORDER BY p.`manufacturer_id` ASC
LIMIT 0, 30
回答by Yehuda Adler
To solve this use SELECT p.* FROMinstead of SELECT * FROM.
要解决此问题，请使用SELECT p.* FROM而不是SELECT * FROM.
The reason is that phpMyAdmin is adding an ORDER BY to your query for the first column in the results grid. Because of the alias, the code that does this fails.
原因是 phpMyAdmin 将 ORDER BY 添加到您对结果网格中第一列的查询。由于别名，执行此操作的代码失败。
This issue reproduces on phpMyAdmin 4.0.6. I don't know the status on the latest 4.2.5
此问题在 phpMyAdmin 4.0.6 上重现。我不知道最新 4.2.5 的状态
回答by Guest
Open your phpmyadmin. Click on your selected database. Now you have a list of all tables on right side. Click on structure of default_ps_productstable. Now you see a structure of it. Now Click on SQL tab and execute query as 'SELECT * FROM default_ps_products ORDER BY '.
Once you execute this query, Now resolve your problem.
打开你的 phpmyadmin。单击您选择的数据库。现在您在右侧有一个所有表的列表。单击default_ps_products表的结构。现在你看到了它的结构。现在单击 SQL 选项卡并以“SELECT * FROM default_ps_products ORDER BY”的形式执行查询。执行此查询后，现在解决您的问题。
回答by innovative kundan
Your query is fine. there is not any error when i run this query. there is nothing wrong with query.
你的查询没问题。运行此查询时没有任何错误。查询没有任何问题。
SELECT * FROM default_ps_products AS p WHERE p.manufacturer_id = 2it working fine.:)
SELECT * FROM default_ps_products AS p WHERE p.manufacturer_id = 2它工作正常。:)
回答by Harvey
when use @Query(nativeQuery = true), should use underline format ,just like this "Sort.by(Sort.Direction.DESC, "update_time")))", else should use camel properties in entity ,like this "Sort.by(Sort.Direction.DESC, "updateTime")))"
使用时@Query(nativeQuery = true)，应该使用下划线格式，就像这样"Sort.by(Sort.Direction.DESC, "update_time")))"，否则应该在实体中使用骆驼属性，像这样"Sort.by(Sort.Direction.DESC, "updateTime")))"
