MySQL #1054 未知列

2022-01-05 00:00:00 mysql phpmyadmin mysql-error-1054

当运行下面的查询时，我最终在 PHPMyAdmin 中遇到了一个 MYSQL 错误:

When running the query below, I end up with a MYSQL error in PHPMyAdmin: 

#1054 - Unknown column 'wd.Datum' in 'having clause'

此查询属于时间报告应用程序，其中用户每天报告项目的工作时间.有一个表是预期工作日的天数，一个是所有员工的表，一个是包含有关当前工时率信息的表.最后一个表还用于确定用户何时受雇.这个想法是获取所有工作日和所有用户，以便(在以后的查询中)选择用户忘记报告时间的日期.我想将结果集限制为用户受雇的天数.这是我的查询:

This query belongs in a time reporting application, where users report time worked on projects on a daily basis. There's a table for days that are expected working days, a table of all employees and a table with information about the current work rate. The last table is also used to determine when a user was employed. The idea is to get all working days and all users in order to (in a later query) select days that a user has forgotten to report times for. I want to limit the result set to days that users has been employed. Here's my query:

SELECT emp.ID AS user
FROM (workdays wd, employees emp)
     INNER JOIN workrates wr ON (emp.ID=wr.UserId)
WHERE (wd.Datum<'2012-11-15')
GROUP BY WEEK(wd.Datum, 3), user
HAVING wd.Datum>=MIN(wr.FromDate)

(可能与http://bugs.mysql.com/bug.php有关?id=13551 这是关于 MySQL 版本 5 中引入的语法更改，如果您忘记某些括号会导致此消息)

(May be related to http://bugs.mysql.com/bug.php?id=13551 which is about a syntax change introduced in MySQL version 5, that causes this message if you forget certain parenthesis)

MySQL 服务器在 Debian 上运行版本5.1.63-0+squeeze1".

The MySQL server is running version "5.1.63-0+squeeze1" on Debian.

EDIT:我将第一个查询行更改为

EDIT: I changed the first query row to

SELECT emp.ID AS user, wd.Datum

按照 Vijay 的建议，查询有效！虽然我不明白为什么.

as suggested by Vijay, and the query works! Though I don't understand why.

推荐答案

您在查询的 HAVING 子句中使用的每一列都必须出现在所选列中.HAVING 适用于计算出的值(例如，如果您使用 SUM() 函数，您可以在 HAVING 子句中使用计算出的总和.)

Every column you use in the HAVING clause of your query must be present in the selected columns. HAVING works on the calculated values (for instance if you use the SUM() function you can use the calculated sum in the HAVING clause.)

另请注意，如果您为列指定别名，则必须在 HAVING 子句中使用别名.

Also note that if you give a column an alias you have to use the alias in the HAVING clause.

有关隐藏列和 HAVING 的更多信息:http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

For more information on hidden columns and HAVING: http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

相关文章