두 가지 기준 SQL을 기준으로 특정 행 위와 아래(인접 행)의 행 가져오기

두 가지 기준 SQL을 기준으로 특정 행 위와 아래(인접 행)의 행 가져오기

예를 들어 다음과 같은 테이블이 있습니다.

+---+-------+------+---------------------+
|id | level |score |      timestamp      |
+---+-------+------+---------------------+
| 4 |   1   |  70  | 2021-01-14 21:50:38 |
| 3 |   1   |  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 |
+---+-------+------+---------------------+

이 중 5개의 행을 테이블(html)에 표시하고 가운데에 특정 행(예: ID=5)이 있고 위와 아래에 두 개의 행(올바른 순서)이 있습니다.레벨=1도 포함됩니다.점수 게시판과 비슷하지만 위와 아래 두 개의 점수만 표시합니다.따라서 점수가 같을 수 있기 때문에 타임스탬프 열도 사용해야 합니다. 따라서 두 점수가 같을 경우 점수를 받은 첫 번째 사용자가 다른 사용자보다 위에 표시됩니다.

예: 사용자가 id=5라고 말하면 표시합니다.

+---+-------+------+---------------------+
|id | level |score |      timestamp      |
+---+-------+------+---------------------+
| 4 |   1   |  70  | 2021-01-14 21:50:38 |
| 8 |   1   |  55  | 2021-01-14 09:20:00 |
| 5 |   1   |  50  | 2021-01-13 12:32:11 |
| 7 |   1   |  50  | 2021-01-14 17:15:20 |
| 2 |   1   |  45  | 2021-01-15 10:50:38 |
| 1 |   1   |  20  | 2021-01-14 13:10:12 |
+---+-------+------+---------------------+

id=7이 id=5보다 낮습니다.

저는 이것을 하는 방법을 아는 사람이 있는지 궁금합니다.

아래에서 시도해 보았지만 필요한 것이 출력되지 않습니다(level_id=2와 id=5가 출력되는 것이고 다른 행은 순서가 맞지 않습니다).

((SELECT b.* FROM table a JOIN table b ON b.score > a.score OR (b.score = a.score AND b.timestamp < a.timestamp)
  WHERE a.level_id = 1 AND a.id = 5 ORDER BY score ASC, timestamp DESC LIMIT 3)
 UNION ALL 
 (SELECT b.* FROM table a JOIN table b ON b.score < a.score OR (b.score = a.score AND b.timestamp > a.timestamp)
  WHERE a.level_id = 1 AND a.id = 5 ORDER BY score DESC, timestamp ASC LIMIT 2)) 
order by score 

표의 모든 행을 출력하는 것이 더 쉬운 경우, 레벨 = 1이라고 말하면 전체 스코어보드입니다.그리고 나서 PHP를 사용하여 특정 행과 그 위와 아래에 있는 두 개의 행을 얻는 것도 알고 싶습니다 :)! (아마도 이것이 SQL을 더 단순하게 유지할 수 있을 것이라고 생각하나요?

다음과 같이 cte 및 내부 조인을 사용할 수 있습니다.

With cte as
(select t.*,
        dense_rank() over (order by score) as dr
   from your_table t)
Select c.*
  From cte c join cte cu on c.dr between cu.dr - 2 and cu.dr + 2
 Where cu.id = 5
Ordwr by c.dr, c.timestamp

창 기능을 제안합니다.

select t.*
from (select t.*,
             max(case when id = 7 then score_rank end) over () as id_rank
      from (select t.*,
                   dense_rank() over (order by score) as score_rank
            from t
            where level = 1
           ) t
     ) t
where score_rank between id_rank - 2 and id_rank + 2;

참고: 이렇게 하면 5개의 고유한 점수 값이 반환되므로 중복 항목에 따라 행이 더 많아질 수 있습니다.

여기에 db<>fidle이 있습니다.

편집:

타임스탬프를 사용하여 정확히 5개의 행을 원하는 경우:

select t.*
from (select t.*,
             max(case when id = 7 then score_rank end) over () as id_rank
      from (select t.*,
                   dense_rank() over (order by score, timestamp) as score_rank
            from t
            where level = 1
           ) t
     ) t
where score_rank between id_rank - 2 and id_rank + 2
order by score;

참고: 이 경우에도 동일한 타임스탬프를 동일하게 취급하지만 데이터에서 고유한 것으로 보입니다.

언급URL : https://stackoverflow.com/questions/65739264/get-rows-above-and-below-neighbouring-rows-a-certain-row-based-on-two-criteri