MySQL#1054未知列 [英] MySQL #1054 unknown column

查看:62
本文介绍了MySQL#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: 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的更多信息:

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

这篇关于MySQL#1054未知列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆