复杂查询max();

Question

我的查询:

SELECT  GENRE,MAX(COUNT(GENRE)) 
FROM BOOKS
WHERE ID_BOOK IN(SELECT ID_BOOK 
                 FROM SIGNATURES
                 WHERE SIGNATURE IN(SELECT SIGNATURE 
                                    FROM ORDERS 
                                    WHERE ID_READER=1))
GROUP BY GENRE;

我得到一个错误:

SQL Error: ORA-00937: not a single-group group function

当我删除GENRE时，它停留在SELECT之后，它工作正常，但只产生数字。我还需要知道哪种特定类型具有最大值。

编辑:

select GENRE,CNT INTO var_genre, var_max from (
SELECT GENRE,COUNT(GENRE) as CNT FROM BOOKS
WHERE ID_BOOK IN(SELECT ID_BOOK FROM SIGNATURES
WHERE SIGNATURE IN(SELECT SIGNATURE FROM ORDERS WHERE ID_READER=1))
GROUP BY GENRE
order by COUNT(GENRE) desc)
where rownum < 2;

Answer 1

您不能在一个查询中执行两个聚合。您应该在subquery中执行:

select distinct GENRE,CNT from ( 
  select GENRE,CNT, DENSE_RANK() Over(prder by CNT) as rnk from (
    SELECT GENRE,
           COUNT(GENRE) over (partition by GENRE) as CNT
      FROM BOOKS
     WHERE ID_BOOK IN(SELECT ID_BOOK 
                       FROM SIGNATURES
                       WHERE SIGNATURE IN(SELECT SIGNATURE FROM ORDERS WHERE ID_READER=1))
)
whre rnk = 1;

或者如果您只需要一行，您可以将其封装到子查询中:

select GENRE,CNT from (
SELECT GENRE,COUNT(GENRE) as CNT FROM BOOKS
WHERE ID_BOOK IN(SELECT ID_BOOK FROM SIGNATURES
    WHERE SIGNATURE IN(SELECT SIGNATURE FROM ORDERS WHERE ID_READER=1))
GROUP BY GENRE
order by COUNT(GENRE) desc)
whre rownum < 2;