【SQL干货】一条语句搞定订单的排序

-- 订单表， 注意状态字段取值是字符串
DROP TABLE IF EXISTS order_detail;
CREATE TEMPORARY TABLE `order_detail`(
`order_no` CHAR(3) COMMENT '订单号',
`status` VARCHAR(16) COMMENT '订单状态，字段值为字符串，提高可读和可维护性：NEW-新单; PAID-已付款; DELIVERD-已发货; FINISHED-已完成',
`amt` INT COMMENT '订单金额，单位：分'
);

-- 订单数据
INSERT INTO order_detail
SELECT '001', 'NEW', 700 UNION ALL
SELECT '002', 'PAID', 7900 UNION ALL
SELECT '003', 'DELIVERD', 8000 UNION ALL
SELECT '004', 'FINISHED', 10000 UNION ALL
SELECT '005', 'PAID', 1380 UNION ALL
SELECT '006', 'FINISHED', 950;

SELECT * FROM order_detail;

 

-- §§§【按特定的状态顺序排序】--新单排在前面，然后是 已付款、已发货、 已完成

-- 直接执行 SELECT * FROM order_detail ORDER BY STATUS; 不满足我们的要求。所以，需要用下面的sql
SELECT *
FROM order_detail
ORDER BY CASE STATUS WHEN 'NEW' THEN 1 WHEN 'PAID' THEN 2 WHEN 'DELIVERD' THEN 3 ELSE 4 END;