mysql语句case when 的代码片段

最新推荐文章于 2024-10-08 12:05:20 发布

zihandan

最新推荐文章于 2024-10-08 12:05:20 发布

阅读量513

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签： sql

本文链接：https://blog.youkuaiyun.com/zihandan/article/details/52700532

mysql 专栏收录该内容

8 篇文章

订阅专栏

本文介绍了一种通过SQL查询来优化订单系统中不同状态的时间显示方法。根据订单的不同状态，选择相应的日期字段，并将其转换为更友好的时间描述，如“今天”、“昨天”等，提升了用户体验。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT
CASE WHEN a.order_status =1 THEN 
(CASE WHEN TO_DAYS(NOW()) - TO_DAYS(a.create_date) = 0 THEN CONCAT('今天',' ',DATE_FORMAT(a.create_date,'%H:%i')) 
WHEN TO_DAYS(NOW()) - TO_DAYS(a.create_date) = 1 THEN CONCAT('昨天',' ',DATE_FORMAT(a.create_date,'%H:%i')) 
ELSE DATE_FORMAT(a.create_date,'%y/%m/%d %H:%i') END )
WHEN  a.order_status =2 THEN 
(CASE WHEN TO_DAYS(NOW()) - TO_DAYS(a.receive_date) = 0 THEN CONCAT('今天',' ',DATE_FORMAT(a.receive_date,'%H:%i')) 
WHEN TO_DAYS(NOW()) - TO_DAYS(a.receive_date) = 1 THEN CONCAT('昨天',' ',DATE_FORMAT(a.receive_date,'%H:%i')) 
ELSE DATE_FORMAT(a.receive_date,'%y/%m/%d %H:%i') END )
WHEN  a.order_status =3 THEN 
(CASE WHEN TO_DAYS(NOW()) - TO_DAYS(a.pay_date) = 0 THEN CONCAT('今天',' ',DATE_FORMAT(a.pay_date,'%H:%i')) 
WHEN TO_DAYS(NOW()) - TO_DAYS(a.pay_date) = 1 THEN CONCAT('昨天',' ',DATE_FORMAT(a.pay_date,'%H:%i')) 
ELSE DATE_FORMAT(a.pay_date,'%y/%m/%d %H:%i') END )
WHEN  a.order_status =4 THEN 
(CASE WHEN TO_DAYS(NOW()) - TO_DAYS(a.get_goods_date) = 0 THEN CONCAT('今天',' ',DATE_FORMAT(a.get_goods_date,'%H:%i')) 
WHEN TO_DAYS(NOW()) - TO_DAYS(a.get_goods_date) = 1 THEN CONCAT('昨天',' ',DATE_FORMAT(a.get_goods_date,'%H:%i')) 
ELSE DATE_FORMAT(a.get_goods_date,'%y/%m/%d %H:%i') END )
WHEN  a.order_status =5 THEN 
(CASE WHEN TO_DAYS(NOW()) - TO_DAYS(a.arrive_date) = 0 THEN CONCAT('今天',' ',DATE_FORMAT(a.arrive_date,'%H:%i')) 
WHEN TO_DAYS(NOW()) - TO_DAYS(a.arrive_date) = 1 THEN CONCAT('昨天',' ',DATE_FORMAT(a.arrive_date,'%H:%i')) 
ELSE DATE_FORMAT(a.arrive_date,'%y/%m/%d %H:%i') END )
WHEN  a.order_status =6 THEN 
(CASE WHEN TO_DAYS(NOW()) - TO_DAYS(a.pay_date) = 0 THEN CONCAT('今天',' ',DATE_FORMAT(a.pay_date,'%H:%i')) 
WHEN TO_DAYS(NOW()) - TO_DAYS(a.pay_date) = 1 THEN CONCAT('昨天',' ',DATE_FORMAT(a.pay_date,'%H:%i')) 
ELSE DATE_FORMAT(a.pay_date,'%y/%m/%d %H:%i') END )
WHEN  a.order_status =7 THEN 
(CASE WHEN TO_DAYS(NOW()) - TO_DAYS(a.confirm_goods_date) = 0 THEN CONCAT('今天',' ',DATE_FORMAT(a.confirm_goods_date,'%H:%i')) 
WHEN TO_DAYS(NOW()) - TO_DAYS(a.confirm_goods_date) = 1 THEN CONCAT('昨天',' ',DATE_FORMAT(a.confirm_goods_date,'%H:%i')) 
ELSE DATE_FORMAT(a.confirm_goods_date,'%y/%m/%d %H:%i') END )
WHEN  a.order_status =8 THEN 
(CASE WHEN TO_DAYS(NOW()) - TO_DAYS(a.finish_date) = 0 THEN CONCAT('今天',' ',DATE_FORMAT(a.finish_date,'%H:%i')) 
WHEN TO_DAYS(NOW()) - TO_DAYS(a.finish_date) = 1 THEN CONCAT('昨天',' ',DATE_FORMAT(a.finish_date,'%H:%i')) 
ELSE DATE_FORMAT(a.finish_date,'%y/%m/%d %H:%i') END )
WHEN  a.order_status =9 THEN 
(CASE WHEN TO_DAYS(NOW()) - TO_DAYS(a.receive_cancel_date) = 0 THEN CONCAT('今天',' ',DATE_FORMAT(a.receive_cancel_date,'%H:%i')) 
WHEN TO_DAYS(NOW()) - TO_DAYS(a.receive_cancel_date) = 1 THEN CONCAT('昨天',' ',DATE_FORMAT(a.receive_cancel_date,'%H:%i')) 
ELSE DATE_FORMAT(a.receive_cancel_date,'%y/%m/%d %H:%i') END )
WHEN  a.order_status =10 THEN 
(CASE WHEN TO_DAYS(NOW()) - TO_DAYS(a.send_cancel_date) = 0 THEN CONCAT('今天',' ',DATE_FORMAT(a.send_cancel_date,'%H:%i')) 
WHEN TO_DAYS(NOW()) - TO_DAYS(a.send_cancel_date) = 1 THEN CONCAT('昨天',' ',DATE_FORMAT(a.send_cancel_date,'%H:%i')) 
ELSE DATE_FORMAT(a.send_cancel_date,'%y/%m/%d %H:%i') END )
WHEN  a.order_status =11 THEN 
(CASE WHEN TO_DAYS(NOW()) - TO_DAYS(a.undo_date) = 0 THEN CONCAT('今天',' ',DATE_FORMAT(a.undo_date,'%H:%i')) 
WHEN TO_DAYS(NOW()) - TO_DAYS(a.undo_date) = 1 THEN CONCAT('昨天',' ',DATE_FORMAT(a.undo_date,'%H:%i')) 
ELSE DATE_FORMAT(a.undo_date,'%y/%m/%d %H:%i') END )
END AS showDate
FROM
  order_info  a

<pre name="code" class="sql">SELECT a.order_status ,a.create_date,
CASE WHEN a.order_status =1 THEN (CASE WHEN TO_DAYS(NOW()) - TO_DAYS(a.create_date) > 1 THEN 1 ELSE 0 END)
WHEN a.order_status = 2 THEN (CASE WHEN TO_DAYS(NOW()) - TO_DAYS(a.receive_date) > 1 THEN 1 ELSE 0 END)
WHEN a.order_status = 4 THEN (CASE WHEN TO_DAYS(NOW()) - TO_DAYS(a.get_goods_date) > 1 THEN 1 ELSE 0 END)
WHEN a.order_status = 5 THEN (CASE WHEN TO_DAYS(NOW()) - TO_DAYS(a.arrive_date) > 1 THEN 1 ELSE 0 END)
WHEN a.order_status = 6 THEN (CASE WHEN TO_DAYS(NOW()) - TO_DAYS(a.pay_date) > 1 THEN 1 ELSE 0 END)
WHEN a.order_status = 8 THEN (CASE WHEN TO_DAYS(NOW()) - TO_DAYS(a.finish_date) > 1 THEN 1 ELSE 0 END)
WHEN a.order_status = 9 THEN (CASE WHEN TO_DAYS(NOW()) - TO_DAYS(a.receive_cancel_date) > 1 THEN 1 ELSE 0 END)
WHEN a.order_status = 10 THEN (CASE WHEN TO_DAYS(NOW()) - TO_DAYS(a.send_cancel_date) > 1 THEN 1 ELSE 0 END)
WHEN a.order_status = 11 THEN (CASE WHEN TO_DAYS(NOW()) - TO_DAYS(a.undo_date) > 1 THEN 1 ELSE 0 END)
WHEN a.order_status = 3 THEN (CASE WHEN TO_DAYS(NOW()) - TO_DAYS(a.pay_date) > 1 THEN 1 ELSE 0 END)
WHEN a.order_status = 7 THEN (CASE WHEN TO_DAYS(NOW()) - TO_DAYS(a.confirm_goods_date) > 1 THEN 1 ELSE 0 END)
END AS dateType
FROM
  order_info a