mysql - 在 SELECT 中返回上面 2 行和前面 2 行

Question

friend 们，

我想创建一个查询以返回所选 ID 上方 2 行和下方 2 行，但必须按排名进行比较。

创建排行榜的查询。

SET @rowId :=0;
SELECT 
    @rowid:= @rowid + 1 AS ranking, 
    tabelaCompleta.*
FROM
(SELECT 
    tbl_timeCartola.nomeTime AS nomeTime, 
    SUM( tbl_ponto.ponto ) AS totalPontos, 
    tbl_timeCartola.FK_loginID
FROM tbl_ponto
INNER JOIN tbl_timeCartola ON tbl_timeCartola.FK_loginID = tbl_ponto.FK_loginID
WHERE tbl_timeCartola.FK_loginID IN ( SELECT FK_loginID FROM tbl_campeonatoUsuario WHERE FK_campeonatoID = '1' )
GROUP BY tbl_timeCartola.nomeTime
ORDER BY totalPontos DESC ) tabelaCompleta;

此查询返回:

+---------+-------------------+-------------+------------+
| ranking | nomeTime          | totalPontos | FK_loginID |
+---------+-------------------+-------------+------------+
|       1 | Mathemio Greus-SB |      612.90 |          7 |
|       2 | CR Hipotenusa VG  |      572.67 |          4 |
|       3 | Zica Danada       |      549.20 |          6 |
|       4 | FC LEEDS UNITED   |      516.12 |          8 |
|       5 | Bradock  F.C      |      503.51 |          5 |
+---------+-------------------+-------------+------------+

Answer 1

试试这个代码。排名是在两个不同的内联 View 中得出的。然后，使用排名将它们连接起来(如果第二个内联 View 的排名比第一个内联 View 的排名为 -1、= 或 +1)。数据根据 selectedId 进行过滤。

SET @rowId1 :=0;
SET @rowId2 :=0;
SET @selectedId := 8;

SELECT
    tabela_ordenada2.*
FROM
(
SELECT 
    @rowId1 := @rowId1 + 1 AS ranking, 
    tabelaCompleta.*
FROM
(SELECT 
    tbl_timeCartola.nomeTime AS nomeTime, 
    SUM( tbl_ponto.ponto ) AS totalPontos, 
    tbl_timeCartola.FK_loginID AS FK_loginID
FROM tbl_ponto
INNER JOIN tbl_timeCartola ON tbl_timeCartola.FK_loginID = tbl_ponto.FK_loginID
WHERE tbl_timeCartola.FK_loginID IN ( SELECT FK_loginID FROM tbl_campeonatoUsuario WHERE FK_campeonatoID = '1' )
GROUP BY tbl_timeCartola.nomeTime
ORDER BY totalPontos DESC ) tabelaCompleta
) tabela_ordenada1
INNER JOIN
(
SELECT 
    @rowId2 := @rowId2 + 1 AS ranking, 
    tabelaCompleta.*
FROM
(SELECT 
    tbl_timeCartola.nomeTime AS nomeTime, 
    SUM( tbl_ponto.ponto ) AS totalPontos, 
    tbl_timeCartola.FK_loginID AS FK_loginID
FROM tbl_ponto
INNER JOIN tbl_timeCartola ON tbl_timeCartola.FK_loginID = tbl_ponto.FK_loginID
WHERE tbl_timeCartola.FK_loginID IN ( SELECT FK_loginID FROM tbl_campeonatoUsuario WHERE FK_campeonatoID = '1' )
GROUP BY tbl_timeCartola.nomeTime
ORDER BY totalPontos DESC ) tabelaCompleta
) tabela_ordenada2
ON (tabela_ordenada2.ranking = tabela_ordenada1.ranking - 1 OR tabela_ordenada2.ranking = tabela_ordenada1.ranking OR tabela_ordenada2.ranking = tabela_ordenada1.ranking + 1)
WHERE tabela_ordenada1.FK_loginID = @selectedId;