MySQL-sql字符型字段排序问题?

MySQL-sql字符型字段排序问题?

浮生未歇 发布于 2017-09-08 字数 240 浏览 1107 回复 4

建立如下表:

CREATE TABLE `order` (
`order_id` char(10) NOT NULL,
`total_pay` int(10) NOT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

如果你对这篇文章有疑问,欢迎到本站 社区 发帖提问或使用手Q扫描下方二维码加群参与讨论,获取更多帮助。

扫码加入群聊

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

偏爱自由 2017-10-18 4 楼

 SELECT * FROM `order` ORDER BY (order_id +1) desc

+----------+-----------+
| order_id | total_pay |
+----------+-----------+
| 12 | 112 |
| 11 | 111 |
| 4 | 110 |
| 3 | 110 |
| 2 | 130 |
| 1 | 120 |
| a | 112 |
+----------+-----------+

清晨说ぺ晚安 2017-10-13 3 楼

SELECT *
FROM `order`
ORDER BY CAST( order_id AS UNSIGNED ) DESC , order_id DESC

SELECT `order_id` , `total_pay`
FROM `order`
ORDER BY CAST( IF( `order_id` +0 >0, `order_id` , (
SELECT max( `order_id` +0 )
FROM `order` ) +1 ) AS UNSIGNED
), order_id

浮生未歇 2017-09-17 2 楼

 SELECT * FROM `order` ORDER BY (case when trim(order_id) REGEXP '^[0-9]+$' then order_id+0 else ascii(order_id) end);

浮生未歇 2017-09-13 1 楼

SELECT *FROM orderORDER BY CAST( order_id AS UNSIGNED ) DESC , order_id DESC