MySQL常用SQL摘录
程序员文章站
2022-07-09 10:22:41
...
-- 目标：超出订单超过48小时不发货的订单，时间内容添加<a>标签
-- 使用函数：substr截图字符串，timediff计算两个时间差，case when条件判断，
-- concat拼接字符串，date_format格式化时间,position:寻找关键字index
select
case when SUBSTR(timediff(now(),DATE_FORMAT(oi.META_PAY_TIME,'%Y-%m-%d %H:%i:%s')),1,
position(':' IN timediff(now(),DATE_FORMAT(oi.META_PAY_TIME,'%Y-%m-%d %H:%i:%s')))-1)>48
and oi.META_ORDER_STATE='active'
and META_PAY_STATUS='2'
and (META_SEA_STATUS in ('1b','1','2b'))
then CONCAT('<a class=light_48H>',DATE_FORMAT(oi.META_PAY_TIME,'%Y-%m-%d %H:%i:%s'),'</a>')
else DATE_FORMAT(oi.META_PAY_TIME,'%Y-%m-%d %H:%i:%s')
end as CREATED_STAMP转载于:https://my.oschina.net/moks/blog/549982
上一篇： javascript查看浏览器