sql - 如何在 count() 之后选择最大值 |甲骨文

Question

这是我的查询:

SELECT f.name, COUNT(*) as num_books
from author f
JOIN book b on b.tittle = f.book
Group by f.name

这给了我这张表:

NAME                                                NUM_BOOKS
-------------------------------------------------- ----------
Dyremann                                                    2
Nam mann                                                    1
Thomas                                                      1
Asgeir                                                      1
Tullemann                                                   5
Plantemann                                                  1
Beste forfatter                                             1
Fagmann                                                     5
Lars                                                        1
Hans                                                        1
Svein Arne                                                  1

如何轻松更改查询以仅显示已发行图书数量最多的作者？ (同时记住我对 sql 很陌生)

Answer 1

Oracle，据我所知——只有 Oracle，允许您嵌套两个聚合函数。

SELECT max (f.name) keep (dense_rank last order by count (*)) as name
from author f
JOIN book b on b.tittle = f.book
Group by f.name

为了获得ALL顶级作者:

select   name
from    (SELECT f.name,rank () over (order by count(*) desc) as rnk
         from author f
         JOIN book b on b.tittle = f.book
         Group by f.name
         ) 
 where   rnk = 1

从 Oracle 12c 开始:

SELECT f.name
from author f
JOIN book b on b.tittle = f.book
Group by f.name
order by count (*) desc
fetch first row /* with ties (optional, in order to get all top authors) */