mysql 不带ORDER BY的嵌套选择中的max()值

nxagd54h 于 2023-06-21 发布在 Mysql

关注(0)|答案(4)|浏览(133)

我发现这个问题快把我逼疯了，（编辑：）因为我试图在没有方法顺序的情况下实现这一点。我有一张table：

- BookAuthors**（书，作者）

book     author
------ ---------
1        Joy McBean
2        Marti McFly
2        Joahnn Strauss
2        Steven Spoilberg
1        Quentin Toronto
3        Dr E. Brown

书和作者都是关键。
现在，我想选择不同“作者”数量最多的“书”值和作者数量。在我们的例子中，查询应该检索作者数为3的'book' 2。

book        authors
-------- ------------
2            3

我已经能够对它们进行分组，并通过以下查询获得每本书的作者数量：

select B.book, count(B.author) as authors
from BookAuthors B
group by B.book

这将导致：

book        authors
-------- -------------
1              2
2              3
3              1

现在我只想获得作者数量最多的那本书。这是我尝试过的查询之一：

select Na.libro, Na.authors from (
    select B.book, count(B.author) as authors
    from BookAuthors B
    group by B.book
    ) as Na
where Na.authors in (select max(authors) from Na)

和/或

select Na.libro, Na.authors from (
    select B.book, count(B.author) as authors
    from BookAuthors B
    group by B.book
    ) as Na
having max( Na.authors)

我有点挣扎...
谢谢你的帮助。
编辑：由于@Sebas很好地回答了我的问题并扩展了我的问题，以下是我使用CREATE VIEW方法想到的解决方案：

create view auth as
    select A.book, count(A.author)
    from BooksAuthors A
    group by A.book
;

然后

select B.book, B.nAuthors
from auth B 
where B.nAuthors = (select max(nAuthors)
                    from auth)

mysql

来源：https://stackoverflow.com/questions/19749220/max-value-from-a-nested-select-without-order-by

4条答案

按热度按时间

rm5edbpk1#

SELECT cnt.book, maxauth.mx
FROM (
    SELECT MAX(authors) as mx
    FROM 
        (
            SELECT book, COUNT(author) AS authors 
            FROM BookAuthors 
            GROUP BY book
        ) t
    ) maxauth JOIN 
        (
            SELECT book, COUNT(author) AS authors 
            FROM BookAuthors 
            GROUP BY book
        ) cnt ON cnt.authors = maxauth.mx

这个解决方案将更加美观和高效，具有以下视图：

CREATE VIEW v_book_author_count AS 
    SELECT book, COUNT(author) AS authors 
    FROM BookAuthors 
    GROUP BY book
;

然后：

SELECT cnt.book, maxauth.mx
FROM (
    SELECT MAX(authors) as mx
    FROM v_book_author_count 
    ) maxauth JOIN v_book_author_count AS cnt ON cnt.authors = maxauth.mx
;

赞(0）回复(0）举报 2023-06-21

0md85ypi2#

select book, max(authors)
from ( select B.book, count(B.author) as authors 
    from BookAuthors B group by B.book ) 
table1;

我不能尝试这个，因为我没有mysql与我…你试着让我知道...

赞(0）回复(0）举报 2023-06-21

yshpjwxd3#

SELECT book b, COUNT(author) c 
FROM BookAuthors
GROUP BY b
ORDER BY c DESC LIMIT 1;

赞(0）回复(0）举报 2023-06-21

ca1c2owp4#

正如@sebas所指出的，有风景的房间更优雅。然而，我觉得另一个不创建视图的解决方案的实现是：

SELECT book, COUNT(author) AS authors 
FROM BookAuthors 
GROUP BY book
HAVING authors = (
                    SELECT MAX(authors)
                    FROM 
                    (
                       SELECT book, COUNT(author) AS authors 
                       FROM BookAuthors 
                       GROUP BY book
                    ) t
                 )

赞(0）回复(0）举报 2023-06-21

我来回答

mysql 不带ORDER BY的嵌套选择中的max()值

4条答案

相关问题

热门标签

最新问答