從子查詢或派生表中查找第二高的值

我有一個返回以下結果的子查詢。

'c003', '120000.00'
'c002', '90000.00'
'c001', '20000.00'
'c005', '8000.00'
'c004', '5000.00'

我想從中找到第二高的值。如何做呢？

子查詢是：

SELECT SUM(coursefees)
FROM enrollment e INNER JOIN batch b INNER JOIN course c
ON e.batchid = b.batchid AND b.courseid = c.courseid
GROUP BY c.courseid;

正確編寫您的聯接，並在排序後限制結果集，如果它足以讓您獲得第二個值，無論是否有兩個或多個課程具有相同的課程費用總和。如果你需要考慮這一點，它會變得更加複雜。讓我知道…

SELECT SUM(coursefees) AS sum_coursefees
FROM enrollment e INNER JOIN batch b ON e.batchid = b.batchid
INNER JOIN course c ON b.courseid = c.courseid
GROUP BY c.courseid
ORDER BY sum_coursefees DESC
LIMIT 1 OFFSET 1;

更新：

那應該這樣做：

SELECT * FROM (
 SELECT 
 c.courseid, 
 @rank := IF(@prev = SUM(coursefees), @rank, @rank + 1) AS rank,
 @prev := SUM(coursefees) AS sum_coursefees
 FROM enrollment e 
 INNER JOIN batch b ON e.batchid = b.batchid
 INNER JOIN course c ON b.courseid = c.courseid
 , (SELECT @rank:=0, @prev:=NULL) var_init
 GROUP BY c.courseid
 ORDER BY sum_coursefees DESC
) sq
WHERE rank = 2;

但請注意，select 子句中的順序很重要。不要切換列。

解決此問題的另一種方法是編寫一個查詢來查找要排除的最大值，並在第二個查詢的條件中使用它，可能看起來像這樣：

SELECT Max(sum_coursefees) AS fee_2 FROM subquery WHERE sum_coursefees < (SELECT Max(sum_coursefees) AS fee_1 from sum_coursefees);

其中fee_1是全域最大值，fee_2是第二高的值。

通過將WHERE子句設置為限制小於“最大值”的值，該值將被排除並返回第二個最大值。

引用自：https://dba.stackexchange.com/questions/61140