Oracle SQL查询报Ora-00935：求作者ID为6的画作最多的博物馆名称

现有数据库结构如下：

Paintings {
    PAINTING_ID
    PAINTING_NAME
    AUTHOR
    MUSEUM
}
Museums {
    MUSEUM_ID
    MUSEUM_NAME
}

Authors {
    AUTHOR_ID
    AUTHOR_NAME
}

其中Paintings表的AUTHOR和MUSEUM字段为外键，分别关联Authors.AUTHOR_ID和MUSEUMS.MUSEUM_ID。

需求：找出拥有作者ID为6的画作数量最多的博物馆名称。

尝试的SQL语句触发Ora-00935错误，具体语句如下：

SELECT MUSEUM.MUSEUM_NAME
FROM PAINTINGS
INNER JOIN AUTHORS
ON AUTHORS.AUTHOR_ID = PAINTINGS.AUTHOR
INNER JOIN MUSEUMS
ON MUSEUMS.MUSEUM_ID = PAINTINGS.MUSEUM
--WHERE AUTHORS.AUTHOR_ID = 6
GROUP BY MUSEUM_ID
HAVING MAX(COUNT(AUTHORS.AUTHOR_ID = 6)) -- Ora-00935

或

SELECT COUNT()
FROM PAINTINGS
WHERE PAINTINGS.AUTHOR = 6

1. SELECT COUNT()语法非法：COUNT函数必须指定统计对象，比如COUNT(*)或具体列名（如COUNT(PAINTING_ID)）。
2. 嵌套聚合函数不被支持：Oracle不允许在HAVING子句中使用MAX(COUNT(...))这种嵌套聚合写法，逻辑上也不需要——应该先完成分组统计，再基于统计结果筛选最大值。
3. 冗余表关联：Paintings.AUTHOR已经是Authors.AUTHOR_ID的外键，直接用p.AUTHOR = 6就能筛选目标作者的画作，无需关联Authors表。

方法1：子查询统计+最大值筛选 #

先统计每个博物馆的目标画作数量，再找出数量最大的博物馆：

SELECT m.MUSEUM_NAME
FROM (
    SELECT 
        p.MUSEUM,
        COUNT(*) AS painting_count
    FROM PAINTINGS p
    WHERE p.AUTHOR = 6
    GROUP BY p.MUSEUM
) cnt
JOIN MUSEUMS m ON cnt.MUSEUM = m.MUSEUM_ID
WHERE cnt.painting_count = (
    SELECT MAX(painting_count)
    FROM (
        SELECT COUNT(*) AS painting_count
        FROM PAINTINGS p
        WHERE p.AUTHOR = 6
        GROUP BY p.MUSEUM
    )
);

方法2：窗口函数（Oracle 12c及以上版本支持） #

用RANK()窗口函数给博物馆的画作数量排名，直接取排名第一的记录：

SELECT MUSEUM_NAME
FROM (
    SELECT 
        m.MUSEUM_NAME,
        COUNT(*) AS painting_count,
        RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
    FROM PAINTINGS p
    JOIN MUSEUMS m ON p.MUSEUM = m.MUSEUM_ID
    WHERE p.AUTHOR = 6
    GROUP BY m.MUSEUM_NAME
)
WHERE rnk = 1;

内容的提问来源于stack exchange，提问作者VladisS