mysql - 如果表按日期排序，如何在 MySQL 上选择特定行之前的行？

Question

我有一个这样的选择结果:

ID | DATE
----------------
10 | 2014-07-23
7  | 2014-07-24
8  | 2014-07-24
9  | 2014-07-24
1  | 2014-07-25
2  | 2014-07-25
6  | 2014-07-25
3  | 2014-07-26
4  | 2014-07-27
5  | 2014-07-28

以上结果按日期排序。现在，我想选择之前的前一行:

2  | 2014-07-25

这是:

1  | 2014-07-25

如果我不知道确切的 ID，并且如果我想选择前一行，则条件代码必须兼容:

3  | 2014-07-26

这是:

6  | 2014-07-25

我应该使用什么条件？

更新

尝试过这个:

SET @rank=0;
SELECT @rank:=@rank+1 AS rank, t1.*
  FROM table t1

然后我得到了这个:

RANK | ID | DATE
----------------
1    | 10 | 2014-07-23
2    | 7  | 2014-07-24
3    | 8  | 2014-07-24
4    | 9  | 2014-07-24
5    | 1  | 2014-07-25
6    | 2  | 2014-07-25
7    | 6  | 2014-07-25
8    | 3  | 2014-07-26
9    | 4  | 2014-07-27
10   | 5  | 2014-07-28

然后我尝试了这个:

SET @rank=0;
SELECT @rank:=@rank+1 AS rank, t1.*
  FROM table t1
  WHERE rank < 3;

我收到此错误:“where 子句”中的未知列“rank”。

Answer 1

这是一种方法...

 DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(ID INT NOT NULL PRIMARY KEY
,DATE DATE NOT NULL
);

INSERT INTO my_table VALUES
(10 ,'2014-07-23'),
(7   ,'2014-07-24'),
(8   ,'2014-07-24'),
(9   ,'2014-07-24'),
(1   ,'2014-07-25'),
(2   ,'2014-07-25'),
(6   ,'2014-07-25'),
(3   ,'2014-07-26'),
(4   ,'2014-07-27'),
(5   ,'2014-07-28');

SELECT a.id
     , a.date
     , b.id b_id
     , b.date b_date
  FROM 
     ( SELECT x.* 
            , COUNT(*) rank 
         FROM my_table x 
         JOIN my_table y 
           ON (y.date < x.date) 
           OR (y.date = x.date AND y.id <= x.id) 
        GROUP 
           BY x.date
         , x.id
     ) a
  LEFT
  JOIN
     ( SELECT x.* 
            , COUNT(*) rank 
         FROM my_table x 
         JOIN my_table y 
           ON (y.date < x.date) 
           OR (y.date = x.date AND y.id <= x.id) 
        GROUP 
           BY x.date
         , x.id
     ) b
    ON b.rank = a.rank - 1;


+----+------------+------+------------+
| id | date       | b_id | b_date     |
+----+------------+------+------------+
| 10 | 2014-07-23 | NULL | NULL       |
|  7 | 2014-07-24 |   10 | 2014-07-23 |
|  8 | 2014-07-24 |    7 | 2014-07-24 |
|  9 | 2014-07-24 |    8 | 2014-07-24 |
|  1 | 2014-07-25 |    9 | 2014-07-24 |
|  2 | 2014-07-25 |    1 | 2014-07-25 |
|  6 | 2014-07-25 |    2 | 2014-07-25 |
|  3 | 2014-07-26 |    6 | 2014-07-25 |
|  4 | 2014-07-27 |    3 | 2014-07-26 |
|  5 | 2014-07-28 |    4 | 2014-07-27 |
+----+------------+------+------------+

...但您也可以使用变量(更快)来完成此操作。