MySQL:查询过滤列后获取最大值

Question

我有下表

+------------+-------+------------+-----------+
| date       | time  | custom_id  | Number    |
+------------+-------+------------+-----------+
| 01/02/2014 | 01:00 | ABC123     | 15        |
| 01/06/2014 | 02:00 | ABC123     | 11        |
| 01/06/2014 | 00:00 | ABC123     | 17        |
| 01/06/2014 | 01:00 | HEY567     | 28        |
| 01/07/2014 | 01:00 | ABC123     | 17        |
| 01/08/2014 | 00:00 | HEY567     | 28        |
| 01/09/2014 | 14:00 | ABC123     | 17        |
| 01/10/2014 | 22:00 | HEY567     | 28        |
| 01/10/2014 | 23:00 | ABC123     | 17        |
| 01/12/2014 | 18:30 | HEY567     | 28        |
+------------+-------+------------+-----------+

我怎样才能:

1. 选择日期范围，从01/06/2014开始，时间00:00开始，直到01/10/2014 23:00

2. 每天对名为 Number 的列进行从 MAX 到 MIN 的排序

3. 还根据 custom_id 列对表格进行排序

我希望它最终看起来与此类似:

+------------+-------+------------+-----------+
| date       | time  | custom_id  | Number    |
+------------+-------+------------+-----------+
| 01/06/2014 | 01:00 | HEY567     | 28        |
| 01/06/2014 | 00:00 | ABC123     | 17        |
| 01/06/2014 | 02:00 | ABC123     | 11        |
| 01/07/2014 | 01:00 | ABC123     | 17        |
| 01/08/2014 | 00:00 | HEY567     | 28        |
| 01/09/2014 | 14:00 | ABC123     | 17        |
| 01/10/2014 | 22:00 | HEY567     | 28        |
| 01/10/2014 | 23:00 | ABC123     | 17        |
+------------+-------+------------+-----------+

如何使用 MySQL 查询来完成此任务？

提前致谢

SQLFiddle 包含我之前执行的查询:http://sqlfiddle.com/#!2/3d2b9/1

Answer 1

我不确定您的“时间”列是否包含“23:30”等数据，这可能有效:

SELECT date, time, custom_id, Number
FROM traffic
WHERE CONCAT(date, " ", time) BETWEEN "01/06/2014 00:00" AND "01/10/2014 23:00"
ORDER BY date, CAST(Number as SIGNED) DESC, custom_id

通过CONCAT组合“日期”和“时间”。

http://sqlfiddle.com/#!2/f57b7/8