在MySQL中按日期和时间降序排序？
让我们创建一个表，以按升序对日期和时间进行排序。创建表的查询如下-
mysql> create table SortByDateAndTime -> ( -> UserId int, -> UserName varchar(100), -> IssueDate date, -> IssueTime time -> );
使用插入命令将记录插入表中。查询如下-
mysql> insert into SortByDateAndTime values(1,'John','2018-12-16','10:30'); mysql> insert into SortByDateAndTime values(2,'Bob','2018-12-16','10:10'); mysql> insert into SortByDateAndTime values(3,'Carol','2018-12-16','10:20'); mysql> insert into SortByDateAndTime values(4,'Sam','2018-12-16','10:00');
使用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)