select-row-with-max-column-value:设法返回正确答案,但寻找更干净的实现

cetgtptt  于 2021-06-18  发布在  Mysql
关注(0)|答案(2)|浏览(285)

我有两张table book(isbn, title, authorID, genre, pubYear, publisher, rrPrice, avgRating) 以及 orderDetails(oNo, bookISBN, quantity) . 有多个订单有自己的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);
9rbhqvlz

9rbhqvlz1#

“最简单”的表单将使用窗口函数——但这些函数仅在MySQL8+中可用。
在旧版本中,您可以将其表述为:

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)
                          );
0ve6wy6x

0ve6wy6x2#

我就是这样着手解决这个问题的。
选择b.标题, b.pubYear ,count(o.bisbn)as numberoforders from books as b inner join orderdetails as o on b.bisbn=o.bisbn group by b.title,b.pubyear order by numberoforders desc

相关问题