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

cetgtptt 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(288)

我有两张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);

sql mysql group-by Max inner-join

来源：https://stackoverflow.com/questions/52967962/select-row-with-max-column-value-managed-to-return-correct-answer-but-looking-f

2条答案

按热度按时间

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

赞(0）回复(0）举报 2021-06-18

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

赞(0）回复(0）举报 2021-06-18

我来回答

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

2条答案

相关问题

热门标签

最新问答