mysql - MySQL - 从“排行榜”中获得位置
问题描述
我正在尝试构建一个查询,我将在其中获得类似排行榜的结果,然后通过他们的 ID 获取特定玩家在排行榜上的位置,并且仅当玩家isHere = 'true'
(存储在另一个表中)时。
表here
:
userID | isHere |
--------------------
2 | true
--------------------
1 | true
--------------------
3 | false
--------------------
4 | true
表userdata
:
id | data |
------------------------------------
2 | {... "points": 5 ...}
------------------------------------
1 | {... "points": 10 ...}
------------------------------------
3 | {... "points": 2 ...}
------------------------------------
4 | {... "points": 28 ...}
询问:
SET
@row_number = 0;
SELECT
*
FROM
(
SELECT
(@row_number := @row_number +1) AS num,
userdata.id,
userdata.data
FROM
userdata
INNER JOIN
here ON userdata.id = here.userID
WHERE
here.isHere = 'true'
ORDER BY
JSON_EXTRACT(userdata.data,
'$.points') DESC
) AS t
WHERE
t.id = 1
这返回num
1... 因为由于某种原因它按用户 ID/ID 排序。我通过设置为 2 进行了双重检查WHERE t.id =
,而不是返回 3,而是返回 2... 有人知道我的查询有什么问题吗?
注意:我确实尝试让列data
只是BIGINT
带有点值,但发生了同样的问题。这样就消除JSON_EXTRACT()
了任何问题(我认为?)
注意2:运行内部查询仍然按num
列的ID排序,但在PHPMyAdmin中以正确的顺序显示(屏幕截图:https ://gyazo.com/73177e79f4fedd4ec7e09ea0e70a9d2b )
解决方案
所以这是有效的查询:
SET
@row_number = 0;
SELECT
*
FROM
(
SELECT
(@row_number := @row_number +1) AS num,
userdata.id,
userdata.data
FROM
userdata
INNER JOIN
(
SELECT
userdata.id,
userdata.data
FROM
userdata
INNER JOIN
here ON userdata.id = here.userID
WHERE
here.isHere = 'true'
ORDER BY
JSON_EXTRACT(userdata.data,
'$.points') DESC
) AS t ON userdata.id = t.id
) AS t2
WHERE
id = ?
需要所有这些的唯一原因是 MySQL 执行部分查询的顺序。就像括号在数学中按运算顺序使用一样,我SELECT
在这里使用了一个语句。