语法

SELECT 列名 FROM 表名 ORDER BY 列名  ASC|DESC

实例

根据单个字段排序

ORDER BY date DESC :仅以 date 降序

SELECT id, date, time, user, name FROM hexingxing WHERE DATE_SUB(CURDATE(), INTERVAL 15 DAY) <= date ORDER BY date DESC

根据两个字段排序,同时为降序

ORDER BY date DESC,time DESC :先以 date 降序,再以 time 降序

SELECT id, date, time, user, name FROM hexingxing WHERE DATE_SUB(CURDATE(), INTERVAL 15 DAY) <= date ORDER BY date DESC, time DESC

根据两个字段排序,升序和降序

ORDER BY date DESC,time ASC :先以 date 降序,再以 time 升序

SELECT id, date, time, user, name FROM hexingxing WHERE DATE_SUB(CURDATE(), INTERVAL 15 DAY) <= date ORDER BY date DESC, time ASC

根据两个字段排序,升序和降序

ORDER BY date,time DESC:先以 date 升序,再以 time 降序

ORDER BY date ASC,time DESC:先以 date 升序,再以 time 降序

注:以上两条语句作用相同

date,time DESCdate 升序,time 降序,因为默认升序,所以可以不写 ASC

SELECT id, date, time, user, name FROM hexingxing WHERE DATE_SUB(CURDATE(), INTERVAL 15 DAY) <= date ORDER BY date, time DESC

根据中文汉字字段排序,一般为升序

ORDER BY CONVERT(name USING gbk) ASC

UTF8 默认校对集是 utf8_general_ci , 不支持中文字符识别,根据需求可以对 MySQL 强制转换 GBK 中文字符后进行排序。

SELECT id, date, time, user, name FROM hexingxing WHERE DATE_SUB(CURDATE(), INTERVAL 15 DAY) <= date ORDER BY CONVERT(name USING gbk) ASC

根据以上可以类推根据三个字段及更多字段来进行升序或降序显示。

根据指定日期查询符合条件范围的数据

WHERE DATE_SUB(CURDATE(), INTERVAL 15 DAY) <= date

SELECT id, date, time, user, name FROM hexingxing WHERE DATE_SUB(CURDATE(), INTERVAL 15 DAY) <= date

规格

ASC:指定列按升序排列

DESC:指定列按降序排列

默认升序,ASC/DESC 只对后方的第一个列名有效,其他不受影响,仍是默认的升序。

更多用法

按多个列的值排序

ORDER BY 列 1, 列 2

按指定的规则排序

ORDER BY charindex(列值,’ 值 1, 值 2, 值 3′)

注意事项

ORDER BY 必须在 WHERE 条件之后使用


0 条评论

发表回复

Avatar placeholder

您的电子邮箱地址不会被公开。 必填项已用*标注