sql - 无效标识符错误，Oracle

Question

我已经尝试编写此查询 1 小时，但 SQL Developer 总是抛出错误。

SELECT d.driver_name, COUNT(*) AS cnt
  FROM Drivers d
  JOIN Fastest_laps fl ON d.ID_driver = fl.ID_driver
  GROUP BY d.driver_name
  HAVING cnt = MAX(cnt);

904. 00000 - "%s: invalid identifier"

Error at last line, column 20.

所以我想出了另一个解决方案，但是抛出了另一个错误:

SELECT d.driver_name, COUNT(*) as cnt
  FROM Drivers d
  JOIN Fastest_laps fl ON d.ID_driver = fl.ID_driver
  GROUP BY d.driver_name
  HAVING COUNT(*) = MAX(COUNT(*));

935. 00000 - "group function is nested too deeply"

Error at last line, column 25.

编辑:谢谢 gyus，你真棒，几乎所有回复都有效，但我必须选择一个...

Answer 1

使用窗口函数:

SELECT driver_name, cnt
FROM (SELECT d.driver_name, COUNT(*) AS cnt,
             MAX(COUNT(*)) OVER () as MAXcnt
      FROM Drivers d JOIN
           Fastest_laps fl
           ON d.ID_driver = fl.ID_driver
      GROUP BY d.driver_name
     ) d
WHERE cnt = MAXcnt;

您也可以使用 RANK() 或 DENSE_RANK() 来表示:

SELECT driver_name, cnt
FROM (SELECT d.driver_name, COUNT(*) AS cnt,
             RANK() OVER (ORDER BY COUNT(*) DESC) as seqnum
      FROM Drivers d JOIN
           Fastest_laps fl
           ON d.ID_driver = fl.ID_driver
      GROUP BY d.driver_name
     ) d
WHERE seqnum = 1;

这种方法的优点是您可以改用 ROW_NUMBER() 并恰好获得一行，即使多个驱动程序具有相同的最大值也是如此。