新建表如下:
+----+---------------------+------+----------+ | id | sale_date | num | order_id | +----+---------------------+------+----------+ | 1 | 2020-05-12 23:23:12 | 12 | 100 | | 2 | 2020-05-14 23:23:31 | 15 | 100 | | 3 | 2020-05-15 23:23:50 | 1 | 100 | | 4 | 2020-05-16 23:24:14 | 10 | 101 | | 5 | 2020-05-17 23:24:33 | NULL | 101 | +----+---------------------+------+----------+
一 按order_id列默认排序查询:
mysql> SELECT * FROM order_sale_info ORDER BY order_sale_info.order_id; +----+---------------------+------+----------+ | id | sale_date | num | order_id | +----+---------------------+------+----------+ | 1 | 2020-05-12 23:23:12 | 12 | 100 | | 2 | 2020-05-14 23:23:31 | 15 | 100 | | 3 | 2020-05-15 23:23:50 | 1 | 100 | | 4 | 2020-05-16 23:24:14 | 10 | 101 | | 5 | 2020-05-17 23:24:33 | NULL | 101 | +----+---------------------+------+----------+ 5 rows in set
可以看到按sale_date列查询时默认按升序排列,与添加ASC关键字效果一致。
mysql> SELECT * FROM order_sale_info ORDER BY order_sale_info.order_id ASC; +----+---------------------+------+----------+ | id | sale_date | num | order_id | +----+---------------------+------+----------+ | 1 | 2020-05-12 23:23:12 | 12 | 100 | | 2 | 2020-05-14 23:23:31 | 15 | 100 | | 3 | 2020-05-15 23:23:50 | 1 | 100 | | 4 | 2020-05-16 23:24:14 | 10 | 101 | | 5 | 2020-05-17 23:24:33 | NULL | 101 | +----+---------------------+------+----------+ 5 rows in set
二 多列排序查询:
按order_id 升序,num升序排序效果,每个排序字段后边要跟排序的类型(ASC或者DESC),如果排序类型为ASC可以不写,默认为ASC,如上述一情况。
mysql> SELECT * FROM order_sale_info ORDER BY order_sale_info.order_id ASC,order_sale_info.num ASC; +----+---------------------+------+----------+ | id | sale_date | num | order_id | +----+---------------------+------+----------+ | 3 | 2020-05-15 23:23:50 | 1 | 100 | | 1 | 2020-05-12 23:23:12 | 12 | 100 | | 2 | 2020-05-14 23:23:31 | 15 | 100 | | 5 | 2020-05-17 23:24:33 | NULL | 101 | | 4 | 2020-05-16 23:24:14 | 10 | 101 | +----+---------------------+------+----------+ 5 rows in set
按order_id 升序,num降序排序效果,每个排序字段后边要跟排序的类型(ASC或者DESC)。
mysql> SELECT * FROM order_sale_info ORDER BY order_sale_info.order_id ASC,order_sale_info.num DESC; +----+---------------------+------+----------+ | id | sale_date | num | order_id | +----+---------------------+------+----------+ | 2 | 2020-05-14 23:23:31 | 15 | 100 | | 1 | 2020-05-12 23:23:12 | 12 | 100 | | 3 | 2020-05-15 23:23:50 | 1 | 100 | | 4 | 2020-05-16 23:24:14 | 10 | 101 | | 5 | 2020-05-17 23:24:33 | NULL | 101 | +----+---------------------+------+----------+ 5 rows in set
如果不写排序类型,默认为升序,如下:
mysql> SELECT * FROM order_sale_info ORDER BY order_sale_info.order_id,order_sale_info.num DESC; +----+---------------------+------+----------+ | id | sale_date | num | order_id | +----+---------------------+------+----------+ | 2 | 2020-05-14 23:23:31 | 15 | 100 | | 1 | 2020-05-12 23:23:12 | 12 | 100 | | 3 | 2020-05-15 23:23:50 | 1 | 100 | | 4 | 2020-05-16 23:24:14 | 10 | 101 | | 5 | 2020-05-17 23:24:33 | NULL | 101 | +----+---------------------+------+----------+ 5 rows in set
order_id列未声明排序类型,默认为升序,num声明排序规则未DESC,体现为降序。