按降序在 MySQL 中按日期和时间排序?
让我们创建一个按升序对日期和时间进行排序的表。创建表所用的查询如下 -
mysql> create table SortByDateAndTime -> ( -> UserId int, -> UserName varchar(100), -> IssueDate date, -> IssueTime time -> ); Query OK, 0 rows affected (0.60 sec)
使用插入命令将记录插入表中。该查询如下 -
mysql> insert into SortByDateAndTime values(1,'John','2018-12-16','10:30'); Query OK, 1 row affected (0.14 sec) mysql> insert into SortByDateAndTime values(2,'Bob','2018-12-16','10:10'); Query OK, 1 row affected (0.14 sec) mysql> insert into SortByDateAndTime values(3,'Carol','2018-12-16','10:20'); Query OK, 1 row affected (0.10 sec) mysql> insert into SortByDateAndTime values(4,'Sam','2018-12-16','10:00'); Query OK, 1 row affected (0.15 sec)
使用 select 语句从表中显示所有记录的查询如下 -
mysql> select *from SortByDateAndTime;
输出
+--------+----------+------------+-----------+ | UserId | UserName | IssueDate | IssueTime | +--------+----------+------------+-----------+ | 1 | John | 2018-12-16 | 10:30:00 | | 2 | Bob | 2018-12-16 | 10:10:00 | | 3 | Carol | 2018-12-16 | 10:20:00 | | 4 | Sam | 2018-12-16 | 10:00:00 | +--------+----------+------------+-----------+ 4 rows in set (0.00 sec)
以下是按降序对日期和时间进行排序的查询 -
mysql> select UserId,UserName,date(IssueDate) as date1,IssueTime from SortByDateAndTime -> order by date(IssueDate)desc,IssueTime desc;
以下是按排序的日期和时间显示的输出 -
+--------+----------+------------+-----------+ | UserId | UserName | date1 | IssueTime | +--------+----------+------------+-----------+ | 1 | John | 2018-12-16 | 10:30:00 | | 3 | Carol | 2018-12-16 | 10:20:00 | | 2 | Bob | 2018-12-16 | 10:10:00 | | 4 | Sam | 2018-12-16 | 10:00:00 | +--------+----------+------------+-----------+ 4 rows in set (0.00 sec)
或者你可以使用另一个对日期和时间进行排序的查询。该查询如下 -
mysql> select UserId,UserName,date(IssueDate) as date1,IssueTime from SortByDateAndTime -> order by date(IssueDate) desc,IssueTime asc;
输出
+--------+----------+------------+-----------+ | UserId | UserName | date1 | IssueTime | +--------+----------+------------+-----------+ | 4 | Sam | 2018-12-16 | 10:00:00 | | 2 | Bob | 2018-12-16 | 10:10:00 | | 3 | Carol | 2018-12-16 | 10:20:00 | | 1 | John | 2018-12-16 | 10:30:00 | +--------+----------+------------+-----------+ 4 rows in set (0.00 sec)
广告