gpt4 book ai didi

mysql - 'having clause' 中的未知列

转载 作者:IT老高 更新时间:2023-10-29 00:06:01 24 4
gpt4 key购买 nike

我需要在 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
)

但我收到了错误:

# 1054 - Unknown column 'rental.return_date' in 'having clause'

有人知道为什么吗?我使用了一个应该是聚合数据的列。我错过了什么?

最佳答案

如文档中所写

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.

您必须在 select 子句中指定 return_date 和 rental_date。

有两种选择:

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

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 =(
...

两者都应该工作得很好。

关于mysql - 'having clause' 中的未知列,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/36105812/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com