是否可以用LIMIT替代IN? [英] Is there an alternative to IN with LIMIT?

问题描述

我想实现一个更新游戏中高分"或最高10分"的过程.

I want to implement a procedure which updates a "high score" or "top 10 scores" in a game.

我的想法是执行以下代码:

My idea is to execute the following code:

DELETE FROM Highscore
 WHERE scoreId NOT IN
   (SELECT scoreId FROM Highscore ORDER BY value DESC LIMIT 10)

但是然后我得到一个错误，即我使用的MySQL版本不支持IN和LIMIT.

But then I get an error that the version of MySQL I'm using doesn't support IN with LIMIT.

还有另一种方法吗?

推荐答案

另一种解决方法:

DELETE FROM Highscore
WHERE scoreId NOT IN
   (SELECT scoreId FROM Highscore ORDER BY value DESC LIMIT 10)

用来将子查询与LIMIT包围在另一个子查询中

is to enclose the subquery with the LIMIT inside another subquery:

DELETE FROM Highscore
WHERE scoreId NOT IN
   (SELECT scoreId FROM 
      (SELECT scoreId FROM Highscore ORDER BY value DESC LIMIT 10)
      AS tmp
   )

但是我认为最好不要在WHERE子句中将嵌套的子查询与引用结合到要从中删除的同一表中.我认为最好使用JOIN:

But I think it's best not to combine nested subqueries with references, in the WHERE clause, to the same table you want to delete from. I think it's better to use JOIN:

DELETE h
FROM 
      Highscore AS h
  CROSS JOIN
      ( SELECT scoreId FROM Highscore ORDER BY value DESC LIMIT 10
      ) AS tmp
WHERE h.scoreId NOT IN (SELECT scoreId FROM tmp)

甚至更好，如果可以简化的话(使用value上的索引可能会更快)(视情况而定)(请注意，如果第十名):

or even better, if you can make it simpler (and possibly faster with an index on value), as it is possible in your case (Note that the following will keep more than 10 rows if there are ties in the 10th place):

DELETE h
FROM 
      Highscore AS h
  CROSS JOIN
      ( SELECT value 
        FROM Highscore 
        ORDER BY value DESC 
        LIMIT 1 OFFSET 9
      ) AS tmp
WHERE h.value < tmp.value

这篇关于是否可以用LIMIT替代IN?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！