错误 - ORA-00979：不是分组依据表达式，但在我删除 AS "Film Number"时f.film_no有效

Question

SELECT  f.title AS "Film Title",f.film_no AS "Film Number", MIN(c.fee) AS "Lowest Contract Cost"
FROM film f, contract c
WHERE f.film_no = c.film_no
GROUP BY f.title
HAVING MIN(fee) > 7000000;

当我运行它时，它会出现错误ORA-00979：不是GROUP BY表达式，但当我删除"f.film_no AS"film Number"时，它是否有效？

这个根本不起作用

SELECT d.firstnames AS "First Name", COUNT(f.title) AS "Number of Films"
FROM director d, film f
WHERE d.director_id = f.director_id
GROUP BY d.surname
HAVING COUNT(f.title) > 1;

感谢提供的任何帮助

Answer 1

错误是否明显？SELECT中有一个未聚合的列，但该列不在GROUP BY中。因此，您可以使用聚合函数，也可以将其包含在内

此外，千万不要在FROM子句中使用逗号始终使用正确、明确的标准可读的JOIN语法。

因此：

SELECT f.title AS "Film Title", f.film_no AS "Film Number",
MIN(c.fee) AS "Lowest Contract Cost"
FROM film f JOIN
contract c
ON f.film_no = c.film_no
GROUP BY f.title, f.film_no
HAVING MIN(fee) > 7000000;