根據兩個條件 SQL（使用 SUM）獲取特定行上方和下方的行

假設我有一張這樣的桌子：

+----------+---------+------+---------------------+
|student_no|level_id |points|      timestamp      |
+----------+---------+------+---------------------+
|     4    |    1    |  70  | 2021-01-14 21:50:38 |
|     3    |    2    |  90  | 2021-01-12 15:38:0  |
|     1    |    1    |  20  | 2021-01-14 13:10:12 |
|     5    |    1    |  50  | 2021-01-13 12:32:11 |
|     7    |    1    |  50  | 2021-01-14 17:15:20 |
|     8    |    1    |  55  | 2021-01-14 09:20:00 |
|    10    |    2    |  99  | 2021-01-15 10:50:38 |
|     2    |    1    |  45  | 2021-01-15 10:50:38 |
+----------+---------+------+---------------------+

我想要做的是找到每個人（student_no）的總分，並在表格中顯示其中的 5 行，中間有某一行（例如 id=5），上下兩行（以正確的順序 - 最高在頂部）。這就像一個記分板，但只顯示使用者的總分（所有級別），上面兩個，下面兩個。所以因為點數可能相等，所以還需要使用時間戳列 - 所以如果兩個分數相等，那麼第一個獲得分數的人會顯示在另一個人的上方。

我在下面嘗試過，但它沒有輸出我需要的東西。

SELECT 
   student_no, SUM(points)
FROM
   (
   (SELECT 
       student_no, SUM(points), 1 orderby
   FROM student_points a
   HAVING
       SUM(points) > (SELECT SUM(points) FROM student_points WHERE student_no = 40204123)
   ORDER BY SUM(points) ASC LIMIT 3) 
    
    UNION ALL 
    
    (SELECT student_no, SUM(points), 2 orderby
   FROM student_points a
   WHERE student_no = 40204123) 
    
    UNION ALL 
    
    (SELECT student_no, SUM(points), 3 orderby
   FROM student_points a
   HAVING
       SUM(points) <= (SELECT SUM(points) FROM student_points WHERE student_no = 40204123)
           AND student_no <> 40204123
   ORDER BY SUM(points) DESC LIMIT 3)
   ) t1
ORDER BY orderby ASC , SUM(points) DESC

這是我正在嘗試的 dbfiddle： https ://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=5ada81241513c9a0be0b6c95ad0f2947

您應該使用ROW_NUMBER()orLAG()和LEAD()window 函式來獲取相對於目前行的前N ​​行和下 N行。

例如：

WITH leaderboard AS
(
   SELECT student_no, SUM(points) AS points_total, ROW_NUMBER() OVER (ORDER BY SUM(points) DESC, timestamp ASC) AS leaderboard_rank
   FROM student_points
   GROUP BY student_no
),
middle_score AS 
(
   SELECT leaderboard_rank
   FROM leaderboard
   WHERE student_no = 5
)

SELECT L.points_total
FROM leaderboard L
INNER JOIN middle_score M
   ON L.leaderboard_rank >= M.leaderboard_rank - 2
   AND L.leaderboard_rank <= M.leaderboard_rank + 2
ORDER BY L.leaderboard_rank

請注意，我假設當您在範例中說“ eg where id=5 ”時，您指的是studsnt_no，否則您可以替換student_no為您在範例中所指的任何欄位。您可以在此處找到有關如何使用視窗函式的其他範例。

您不能以這種方式使用 sum，因為它將所有內容減少到一行，因此您的查詢中始終需要一個 GROUP BY。

接下來是外部 SELECT：

SELECT  student_no, points

需要具有該名稱的兩列，因此第一個 SELECT 必須將這些名稱作為列名稱或別名

CREATE TABLE `student_points` (
  `student_no` int(9) NOT NULL,
  `level_id` int(11) NOT NULL,
  `points` int(3) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
);

INSERT INTO `student_points` (`student_no`, `level_id`, `points`, `timestamp`) VALUES
(12345678, 1, 80, '2021-01-15 16:07:43'),
(12345678, 2, 25, '2021-01-13 17:15:10'),
(12345678, 3, 90, '2021-01-17 22:41:55'),
(12345678, 4, 90, '2021-01-17 22:41:55'),
(40145489, 1, 85, '2021-01-14 21:50:58'),
(40204123, 1, 80, '2021-01-12 15:37:11'),
(40204123, 2, 75, '2021-01-12 15:38:06'),
(40204123, 3, 30, '2021-01-13 22:13:13'),
(40213894, 1, 90, '2021-01-14 21:52:00'),
(40213894, 2, 95, '2021-01-17 22:42:50'),
(40213894, 4, 100, '2021-01-17 22:42:50'),
(40283947, 1, 57, '2021-01-14 21:50:14'),
(40334891, 1, 95, '2021-01-14 21:54:25'),
(40829379, 1, 70, '2021-01-14 21:50:38'),
(45216227, 1, 97, '2021-01-16 19:05:16');

SELECT 
    student_no, points
FROM
    (
    (SELECT 
        student_no, SUM(points) points, 1 orderby
    FROM student_points a

    GROUP BY student_no
        HAVING
        SUM(points) > (SELECT SUM(points) FROM student_points WHERE student_no = 40204123)
    ORDER BY SUM(points) ASC 
    LIMIT 3) 
     
     UNION ALL 
     
     (SELECT student_no, SUM(points), 2 orderby
    FROM student_points a
    WHERE student_no = 40204123) 
     
     UNION ALL 
     
     (SELECT student_no, SUM(points), 3 orderby
    FROM student_points a

    GROUP BY student_no
    HAVING
        SUM(points) <= (SELECT SUM(points) FROM student_points WHERE student_no = 40204123)
            AND student_no <> 40204123    
    ORDER BY SUM(points) DESC
     
    LIMIT 3)
    ) t1
ORDER BY orderby ASC , points DESC

學生號 | 積分
---------: | -----:
 40213894 | 285
 12345678 | 285
 40204123 | 185
 45216227 | 97
 40334891 | 95
 40145489 | 85

db<>在這裡擺弄

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