mysql - 使用 mysql 从某一行开始选择

Question

如何从特定行开始选择？

比如我要查询的表，

pg_id   pg_title    pg_backdate
1       a           2012-09-18 13:32:49
2       b           2011-09-18 13:32:49
3       c           2011-06-18 13:32:49
4       d           2010-05-18 13:32:49
5       e           2009-04-18 13:32:49
6       f           2011-10-18 13:32:49
7       g           2012-04-18 13:32:49
8       h           2012-09-18 13:32:49
9       i           2012-10-18 13:32:49

我只想选择前 5 行，从当月和当年开始，我已经处理了下面的查询 - 它只选择当月和当年的前 5 行，而不是以后的行，

SELECT *
    FROM root_pages AS p

    WHERE DATE_FORMAT(p.pg_backdate, '%Y') = '2011'
    AND DATE_FORMAT(p.pg_backdate, '%m') = '09' 

    ORDER BY p.pg_backdate DESC

    LIMIT 5

理想情况下，它应该只返回这些行，

pg_id   pg_title    pg_backdate
1       a           2012-09-18 13:32:49
2       b           2011-09-18 13:32:49
6       f           2011-10-18 13:32:49
7       g           2012-04-18 13:32:49
8       h           2012-09-18 13:32:49

Answer 1

SELECT *
FROM root_pages AS p
WHERE p.pg_backdate >= '2011-09-01'
ORDER BY p.pg_backdate DESC
LIMIT 5

像你这样使用函数会扼杀 MySQL 使用索引的任何机会。

如果您想从当月中选择前 5 名，请执行以下操作:

SELECT *
FROM root_pages AS p
WHERE p.pg_backdate BETWEEN '2011-09-01' AND '2011-09-30'
ORDER BY p.pg_backdate DESC
LIMIT 5

如果你想要当月的前 5 名和之后的行，那么做

  SELECT *
  FROM root_pages AS p
  WHERE p.pg_backdate BETWEEN '2011-09-01' AND '2011-09-30'
  ORDER BY p.pg_backdate DESC
  LIMIT 5
UNION ALL
  SELECT *
  FROM root_pages AS p
  WHERE p.pg_backdate > '2011-09-30'
  ORDER BY p.pg_backdate DESC