“having clause”中的未知列 [英] Unknown column in 'having clause'

问题描述

我需要在sakila数据库中找到电影的最长租期。
i有trie：

i need to find in sakila data base the longest rental period of a movie. i have trie this:

  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
    )

但我得到错误：

有人知道为什么吗？我已经使用了一个应该是聚合数据的列..我缺少的

does anybody know why? i have used a column that's supposed to be the aggregated data.. what am i missing

推荐答案

p>

As written in the documentation

SQL标准要求HAVING必须仅引用GROUP BY子句中的列或聚合函数中使用的列。但是，MySQL支持对此行为的扩展，并允许HAVING引用SELECT列表中的列和外部子查询中的列。

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。

You have to specify return_date and rental_date in the select clause.

有两个选项：

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

两者都应该很好。

这篇关于“having clause”中的未知列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！