时间段数据,要求进行中>即将开始>已结束排序

database

今天接到一个需求,任务按照 进行中>即将开始>已结束 >活动任务>全局任务 > 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

以上是 时间段数据,要求进行中&gt;即将开始&gt;已结束排序 的全部内容, 来源链接: utcz.com/z/533208.html

回到顶部