mysql - 选择最大列值为 : Managed to return correct answer but looking for cleaner imlementation 的行

Question

我有两个表book(isbn，标题，authorID，流派，pubYear，出版商，
rrPrice、avgRating) 和 orderDetails(oNo、bookISBN、数量)。有多个订单都有自己的 orderDetails，因此每本书可以有多个与它们关联的 orderDetails 和自己的数量。我需要找到订单最多的书。我已经设法获得正确的输出，但我的解决方案非常困惑，涉及使用相同的子查询两次，我想知道是否有人可以建议一个更干净的实现？我得出的答案如下:

SELECT title, pubYear, orderSum
FROM
(
SELECT title, pubYear,orderSum
FROM 
(
SELECT title, pubYear,SUM(quantity) AS orderSum
FROM book INNER JOIN orderDetails ON bookISBN = isbn
GROUP BY title, pubYear) T
GROUP BY title, pubYear) S
WHERE S.orderSum = (select max(S.orderSum) from (SELECT title, 
pubYear,orderSum
FROM 
(
SELECT title, pubYear,SUM(quantity) AS orderSum
FROM book INNER JOIN orderDetails ON bookISBN = isbn
GROUP BY title, pubYear) T
GROUP BY title, pubYear) S);

Answer 1

“最简单”的形式将使用窗口函数——但这些仅在 MySQL 8+ 中可用。

在旧版本中，您可以将其表述为:

SELECT b.title, b.pubYear, SUM(od.quantity) AS orderSum
FROM book b INNER JOIN
     orderDetails od
     ON od.bookISBN = b.isbn
GROUP BY b.title, b.pubYear, b.isbn
HAVING SUM(od.quantity) = (SELECT SUM(od2.quantity)
                           FROM orderDetails od2
                           WHERE od2.isbn = b.isbn
                           ORDER BY SUM(od2.quantity DESC)
                          );