让我们创建一个表来按升序对日期和时间进行排序。创建表的查询如下 -

mysql> create table SortByDateAndTime   -> (   -> UserId int,   -> UserName varchar(100),   -> IssueDate date,   -> IssueTime time   -> );Query OK, 0 rows affected (0.60 sec)

使用insert命令在表中插入记录。查询如下 -

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 fromSortByDateAndTime   -> 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 fromSortByDateAndTime   -> 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)