SQL：ORDER BY`date`和START WHERE`value` =“something”？
16lz
2021-01-22
SQL Table:
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 |
-----------------------------------------
The following query gives me the results ordered by date:
以下查询给出了按日期排序的结果：
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 |
-----------------------------------------
But what if I wanted to order it by date and also start from a specific 'color'?
但是如果我想按日期订购并从特定的“颜色”开始呢？
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 |
-----------------------------------------
^What is the correct syntax to get this result?
^获得此结果的正确语法是什么？
1 个解决方案
#1
3
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
Updated:
更新：
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
Second update to meet newly discovered criteria.
第二次更新以满足新发现的标准。
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
更多相关文章
- 无法弄清楚mySQL语法错误的来源
- 从Access表将数据导入Excel，从子句中出现语法错误
- 在多个查询和子查询中更正连接语法
- 背景图像颜色检测与Android油漆。
- 根据给出的语料库，训练n-gram模型。根据训练出的模型，判断测试集中
- Android Toolbar返回按钮颜色修改
- Android 自定义控件 改变图片颜色来实现类似selector点击更改颜
- Android开发笔记——改变字体颜色的三种方法
- 更改Edittext光标的颜色与粗细