MySQL “具有条款”中的未知列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36105812/
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 'having clause'
提问by mike
i need to find in sakila data base the longest rental period of a movie. i have trie this:
我需要在 sakila 数据库中找到电影的最长租期。我试过这个：
SELECT DISTINCT
customer.first_name
FROM
rental,
customer
WHERE
rental.customer_id = customer.customer_id
GROUP BY
rental.rental_id
HAVING
(
rental.return_date - rental.rental_date
) =(
SELECT
MAX(countRental)
FROM
(
SELECT
(
rental.return_date - rental.rental_date
) AS countRental
FROM
rental,
customer
GROUP BY
rental.rental_id
) AS t1
)
but i am getting the error:
但我收到错误：
1054 - Unknown column 'rental.return_date' in 'having clause'
1054 - 'have 子句'中的未知列'rental.return_date'
does anybody know why? i have used a column that's supposed to be the aggregated data.. what am i missing
有人知道为什么吗？我使用了一个应该是聚合数据的列..我错过了什么
回答by piotrgajow
As written in the documentation
如文档中所写
The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well.
SQL 标准要求 HAVING 必须仅引用 GROUP BY 子句中的列或聚合函数中使用的列。但是，MySQL 支持此行为的扩展，并允许 HAVING 引用 SELECT 列表中的列和外部子查询中的列。
You have to specify return_date and rental_date in the select clause.
您必须在 select 子句中指定 return_date 和rental_date。
There are two options:
有两种选择：
SELECT DISTINCT
customer.first_name,
rental.return_date,
rental.rental_date
FROM
rental,
customer
WHERE
rental.customer_id = customer.customer_id
GROUP BY
rental.rental_id
HAVING
(
rental.return_date - rental.rental_date
) =(
...
or
或者
SELECT DISTINCT
customer.first_name,
(rental.return_date - rental.rental_date) as rental_duration
FROM
rental,
customer
WHERE
rental.customer_id = customer.customer_id
GROUP BY
rental.rental_id
HAVING
rental_duration =(
...
Both should work just fine.
两者都应该工作得很好。
