mysql - 千范围组中的最高主键

Question

我正在使用 MySQL，我正在尝试获取数据主键的前 1000 个值，如下所示:

+ --------- + ----------- +| id        | name        |+ --------- + ----------- +| 1000      | George      || 1002      | David       || 2000      | Jane        || 2003      | Mary        || 3123      | Steven      || 3222      | Marta       || 10001     | Chris       || 10091     | Nathan      |+ --------- + ----------- +

And I'd like to grab the highest of the 1000s range. So my query would return something like this:

+ --------- + ----------- +| id        | name        |+ --------- + ----------- +| 1002      | David       || 2003      | Mary        || 3222      | Marta       || 10091     | Nath        |+ --------- + ----------- +

This is what I have and it works:

SET @2k:= 2000;
SET @3k:= 3000;
SET @4k:= 4000;
SET @11k:= 11000;

SELECT sub1.id,
       person.name
FROM
  (SELECT max(id) id
   FROM person
   WHERE id < @2k
   UNION 
   SELECT max(id) id
   FROM person
   WHERE id < @3k
   UNION 
   SELECT max(id) id
   FROM person
   WHERE id < @4k
   UNION 
   SELECT max(id) id
   FROM person
   WHERE id < @11k) sub1
JOIN person ON person.id = sub1.id
ORDER BY id ASC

这是 SQLFiddle:http://sqlfiddle.com/#!9/95ef8/14

同样，它可以工作，但是有没有更动态的方式，这样我就不必创建@2k、@3k 等变量并创建 UNIONS？就像如果表增长到数百万个 id，上面将涉及太多重构。

Answer 1

这将获得每组一千个中最高的 key :

SELECT MAX(id) as max_per_thousand
FROM person
GROUP BY FLOOR(id/1000)

然后您可以将它与人员表结合起来以获得每个 ID 的完整记录:

SELECT p.*
FROM person AS p
JOIN (SELECT MAX(id) as maxid
    FROM person
    GROUP BY FLOOR(id/1000)) AS t
ON p.id = t.maxid

DEMO