Max() 是需要在select语句中的聚合函数

SELECT 
  c_firstName
  , c_lastName
  , rental.c_ID
FROM customer, rental
GROUP BY rental.c_ID 
HAVING rental.c_ID=
  (
  select 
    MAX(i.iID) 
  from 
    (
    SELECT 
      COUNT(rental.c_ID) iID
    FROM customer, copies, rentalprocess, rental, film
    WHERE 
      customer.c_ID=rental.c_ID AND 
      rentalprocess.r_ID=rental.r_ID AND
      rentalprocess.s_ID=copies.s_ID AND 
      film.f_ID=copies.f_ID AND
      f_category=3 
      AND r_date LIKE "2016-07%" 
    GROUP BY rental.c_ID
    ) i
  )

在这种情况下，sub select返回多行，但您将获取该查询的最大值
linoff先生的评论是正确的，您应该使用显式连接：

SELECT 
  c_firstName
  , c_lastName
  , rental.c_ID
FROM customer, rental
GROUP BY rental.c_ID 
HAVING rental.c_ID=
  (
  select 
    MAX(i.iID) 
  from 
    (
    SELECT 
      COUNT(rental.c_ID) iID
    FROM 
      customer 
      inner join rental 
        on customer.c_ID=rental.c_ID
      inner join rentalprocess 
        on rentalprocess.r_ID=rental.r_ID
      inner join copies 
        on rentalprocess.s_ID=copies.s_ID
      inner join film on film.f_ID=copies.f_ID
    WHERE 
      f_category=3 
      AND r_date LIKE "2016-07%" 
    GROUP BY rental.c_ID
    ) i
  )

您的代码应该是这样的，在代码中正确地连接表。我不知道哪个列和表最适合这个解决方案，因为我没有得到你的完整模式。但这应该会提供更快的查询。如果愿意，可以在select中添加更多列。

select c_firstName | ' ' | c_lastName, count(rental.c_ID) as rentalCustomer
    from customer
     inner join rental
       on join " connect the both tables"
      innner join rentalprocess
       on "connect rental with rentalprocess"
      inner join copies
         on " connect rentalprocess with copies"
       inner join film
        on "connect copies with film"
    WHERE customer.c_ID=rental.c_ID AND 
    rentalprocess.r_ID=rental.r_ID AND
      rentalprocess.s_ID=copies.s_ID AND 
    film.f_ID=copies.f_ID AND
      f_category=3 AND r_date LIKE "2016-07%"
     group by c_firstName, c_lastName, rental.c_ID
    order by rental.c_ID desc;