让我们创建一个表来按升序对日期和时间进行排序。创建表的查询如下 -
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)
以上就是mysql中按日期和时间降序排序？的详细内容。