mysql - 在 FIND_IN_SET 中使用 MySQL IN 条件

Question

我有一个查询，我使用相当密集的 WHERE IN 子句来获取与玩家相关的 ID 列表。

SELECT p.name,
  0a.stat_value
FROM leaderheadsplayers p
LEFT JOIN leaderheadsplayersdata_alltime 0a
ON 0a.player_id    = p.player_id
AND 0a.stat_type   = 'kills'
WHERE p.player_id IN
  (SELECT player_id
  FROM
    (SELECT 0a.player_id
    FROM leaderheadsplayersdata_alltime 0a
    WHERE 0a.stat_type = 'kills'
    ORDER BY 0a.stat_value DESC LIMIT 0,
      20
    ) 1a
  )

问题是我想通过使用 FIND_IN_SET 来保留最终结果中 IN 子句中这些 ID 的顺序，而无需再次从 IN 子句中执行密集查询。像这样的事情:

SELECT p.name,
  0a.stat_value
FROM leaderheadsplayers p
LEFT JOIN leaderheadsplayersdata_alltime 0a
ON 0a.player_id    = p.player_id
AND 0a.stat_type   = 'kills'
WHERE p.player_id IN
  (SELECT player_id
  FROM
    (SELECT 0a.player_id
    FROM leaderheadsplayersdata_alltime 0a
    WHERE 0a.stat_type = 'kills'
    ORDER BY 0a.stat_value DESC LIMIT 0,
      20
    ) 1a
  )
ORDER BY FIND_IN_SET(p.player_id, result_from_in_clause)

这是我当前的输出:

player_id | stat_value
3 | 304
5 | 507
4 | 208

这就是我想要得到的:

player_id | stat_value
5 | 507
3 | 304
4 | 208

Answer 1

简单的order by应该能够做到:

SELECT p.name,
  0a.stat_value
FROM leaderheadsplayers p
LEFT JOIN leaderheadsplayersdata_alltime 0a
ON 0a.player_id    = p.player_id
AND 0a.stat_type   = 'kills'
WHERE p.player_id IN
  (SELECT player_id
  FROM
    (SELECT 0a.player_id
    FROM leaderheadsplayersdata_alltime 0a
    WHERE 0a.stat_type = 'kills'
    ORDER BY 0a.stat_value DESC LIMIT 0,
      20
    ) 1a
  )
order by 
  0a.stat_value,  p.player_id;