MySQL #1054 unknown column

问题:

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)

(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)

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

EDIT: I changed the first query row to

SELECT emp.ID AS user, wd.Datum

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

回答1:

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.)

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

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

回答2:

Try to use wd.Datum in your select.

Its common in sql, if you are using a column in having clause, you have to add that column in select. What happens is like it will create a virtual table with the columns you have selected and filter with the condition in having clause.

--- Make it as answered if it really answered your question.