我正在尝试将日期表按升序或降序排序,但我也无法做到这一点。查询没有给出语法错误,所以我认为不是这样。
Server version: 5.7.28-0ubuntu0.18.04.4 (Ubuntu)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
mysql> select * from elections order by 'last_election' ASC;
+--------+-----------------+---------------+
| row_id | jurisdiction_id | last_election |
+--------+-----------------+---------------+
| 1 | 3 | 2018-11-24 |
| 2 | 2 | 2019-03-23 |
| 4 | 5 | 2018-03-17 |
| 5 | 4 | 2017-11-25 |
| 6 | 1 | 2016-10-15 |
| 7 | 6 | 2017-03-11 |
| 8 | 7 | 2018-03-03 |
| 9 | 8 | 2016-08-27 |
| 11 | 9 | 2019-05-18 |
+--------+-----------------+---------------+
9 rows in set (0.00 sec)
以前/其他类似的问题都说,"你的字段类型是文本或varchar,duh!"但字段类型肯定是日期:
+----------------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+---------+------+-----+---------+----------------+
| row_id | int(11) | NO | PRI | NULL | auto_increment |
| jurisdiction_id | int(11) | NO | UNI | NULL | |
| last_election | date | YES | | NULL | |
+----------------------+---------+------+-----+---------+----------------+
如何查询表以使last_election井井有条?
我认为应该是:
select * from elections order by `last_election` ASC
注意报价类型
因为 ''代表字符串,反引号代表列名。所以我想您正在尝试按不变的字符串对它们进行排序 - 因此它的工作原理与根本没有order by
相同
您的查询似乎没问题
请尝试以下查询。
select * from elections order by date(last_election) ASC;
如果它的工作,请尝试删除表并使用适当的字段类型重新创建它。