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

朋友们

我想创建一个查询来返回我所选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 |
+---------+-------------------+-------------+------------+

最佳答案

试试这个代码。排名来自两个不同的内联视图。然后，使用排名将它们加入（如果第二个嵌入式视图的排名比第一个嵌入式视图的排名为-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;

关于mysql - 在SELECT中返回上方2行和前方2行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/24770494/