时间段数据,要求进行中>即将开始>已结束排序
今天接到一个需求,任务按照 进行中>即将开始>已结束 >活动任务>全局任务 > sort的排序
表如下
CREATE TABLE `task` ( `taskid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT "任务ID",
`title` varchar(20) NOT NULL DEFAULT "" COMMENT "标题",
`type_for` tinyint(1) NOT NULL DEFAULT "1" COMMENT "1全局2针对活动",
`atid` int(11) unsigned NOT NULL DEFAULT "0" COMMENT "活动ID",
`beg_time` int(11) NOT NULL DEFAULT "0" COMMENT "开始时间",
`end_time` int(11) NOT NULL DEFAULT "0" COMMENT "结束时间",
`target` text COMMENT "任务目标",
`state` tinyint(1) NOT NULL DEFAULT "1" COMMENT "状态1开启,0关闭",
`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT "创建时间",
`updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT "更新时间",
`sort` int(10) unsigned NOT NULL DEFAULT "0" COMMENT "倒序",
PRIMARY KEY (`taskid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT="任务系统"
order by
需要做用到case when
才能正常达到预期
select *
from
`task`
order by
(case
when beg_time <= 1587382508
and 1587382508 <= end_time then 1
else 0 end) desc,
(case
when beg_time > 1587382508 then 1
else 0 end) desc,
(case
when end_time < 1587382508 then 1
else 0 end) desc,
type_for asc,
sort desc
以上是 时间段数据,要求进行中>即将开始>已结束排序 的全部内容, 来源链接: utcz.com/z/533208.html