我正在尝试根据信誉对表进行排序,然后选择行[rank]的位置,这很好,但我想打印出该排名行上方的3个表和下方的3个表格,但LIMIT rank,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
我想你正在寻找一个声誉为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)