mysql - SQL: ORDER BY `date` AND START WHERE`value` ="something"?

Question

SQL 表:

-----------------------------------------
| ID  |   COLOR   |         DATE        |
|-----|-----------|---------------------|
|  1  | ORANGE    | 2011-11-03 01:14:00 |
|  2  | YELLOW    | 2011-11-03 01:13:00 |
|  3  | GREEN     | 2011-11-03 01:16:00 |
|  4  | BLUE      | 2011-11-03 01:16:00 |
|  5  | PINK      | 2011-11-03 01:12:00 |
-----------------------------------------

以下查询为我提供了按日期排序的结果:

SELECT *
FROM `table`
ORDER BY `date` DESC
LIMIT 0, 4

-----------------------------------------
| RESULT:                               |
|---------------------------------------|
|  3  | GREEN     | 2011-11-03 01:16:00 |
|  4  | BLUE      | 2011-11-03 01:16:00 |
|  1  | ORANGE    | 2011-11-03 01:14:00 |
|  2  | YELLOW    | 2011-11-03 01:13:00 |
-----------------------------------------

但如果我想按日期排序并从特定“颜色”开始怎么办？

SELECT *
FROM `table`
ORDER BY `date` DESC
LIMIT 0, 4
START WHERE `color`='blue'

-----------------------------------------
| RESULT I WANT:                        |
|---------------------------------------|
|  4  | BLUE      | 2011-11-03 01:16:00 |
|  1  | ORANGE    | 2011-11-03 01:14:00 |
|  2  | YELLOW    | 2011-11-03 01:13:00 |
|  5  | PINK      | 2011-11-03 01:12:00 |
-----------------------------------------

^得到这个结果的正确语法是什么？

Answer 1

SELECT 
  y.*
FROM
  YourTable y
WHERE
  y.date <= (SELECT yb.date FROM YourTable yb WHERE yb.color = 'BLUE')
ORDER BY
  y.date DESC
LIMIT 4 OFFSET 0

更新:

SELECT 
  y.*
FROM
  YourTable y
WHERE
  /* The colors 'before' blue */
  y.date < (SELECT yb.date FROM YourTable yb WHERE yb.color = 'BLUE') or
  /* And blue itself */
  y.color = 'BLUE'
ORDER BY
  y.date DESC
LIMIT 4 OFFSET 0

第二次更新以满足新发现的标准。

SELECT 
  y.*
FROM
  YourTable y,
  (SELECT yb.id, yb.date FROM yb WHERE color = 'GREEN') ys
WHERE
  /* The colors 'before' green */
  y.date < ys.date or
  /* The colors on the same date as green, but with greater 
     or equal id to green. This includes green itself.
     Note the parentheses here. */
  (y.date = ys.date and y.id >= ys.id)
ORDER BY
  y.date DESC
LIMIT 4 OFFSET 0