php - 按声誉对表格进行排序，然后选择排名 + LIMIT 以打印上方和下方的行

Question

我试图按信誉对表进行排序，然后选择行 [rank] 的位置，它工作正常，但我想打印出此排名行上方的 3 个表格和下方的 3 个表格，但 LIMIT 排名,6 不起作用:/(我对错误格式的 SQL 感到抱歉:/) 将感谢每一个帮助:)

这是有效的:

$query  = mysqli_query($con, "SET @rank=0");
$query  = mysqli_query($con, "
            SELECT 
               rank, 
               user_email, 
               reputation 
            FROM (
               SELECT 
                 @rank:=@rank+1 AS rank, 
                 user_email, 
                 reputation 
               FROM accounts, (SELECT @rank := 0) r 
               ORDER BY 
                 reputation DESC 
                 LIMIT 0,7
            ) t 
            WHERE 
               reputation >= '5' 
               OR reputation < '5'");

打印

[rank] [user_email]       [reputation]
1      mail1@gmail.com    20
2      test@test.com      15
3      mail2@gmail.com    10
4      othermail@gmail.com 5
5      hmmmmm@gmail.com    0
6      ouch@gmail.com      0
7      somemail@gmail.com  0

Answer 1

我假设您正在寻找声誉为 5 的人，并希望该人加上该人排名之上和之下的 2。

在这种情况下，可能会进行选择，因为您本质上必须获得声誉为 5 的人的排名，然后将其加入到重复的相同选择中，以找到两边都有排名的人:-

SELECT Sub1.rank, Sub1.user_email, Sub1.reputation 
FROM 
(
    SELECT @rank2:=@rank2+1 AS rank, user_email, reputation 
    FROM accounts, (SELECT @rank2 := 0) r 
    ORDER BY reputation DESC 
    LIMIT 0,7
) Sub1 
INNER JOIN
(
    SELECT rank, user_email, reputation 
    FROM 
    (
        SELECT @rank:=@rank+1 AS rank, user_email, reputation 
        FROM accounts, (SELECT @rank := 0) r 
        ORDER BY reputation DESC 
        LIMIT 0,7
    ) t 
    WHERE reputation = '5'
) Sub2
ON Sub1.rank BETWEEN (Sub2.rank - 2) AND (Sub2.rank + 2)