验证中...
按照专场状态,并且每个状态内,再分别按照不同条件排序
Raw Copy
先创建一个表:
CREATE TABLE `mt_specialise` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`cover_image` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '轮播图片 最多5张,序列化存储',
`number` int(64) NOT NULL DEFAULT '0' COMMENT '专场编号',
`name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '专场名称',
`start_time` int(11) NOT NULL DEFAULT '0' COMMENT '开始时间',
`expires_time` int(11) NOT NULL DEFAULT '0' COMMENT '结束时间',
`goods_ids` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '商品ID,多个ID用逗号分割存储,用逗号分割的原因是可以支持数据库按单个ID检索',
`status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '状态 0、未上架 1、预览中 2、拍卖中 3、已结束',
`click_times` int(10) NOT NULL DEFAULT '0' COMMENT '围观次数',
`ctime` int(10) NOT NULL DEFAULT '0',
`utime` int(10) NOT NULL DEFAULT '0',
`auction_numbers` int(11) NOT NULL DEFAULT '0' COMMENT '//出价时判断当前商品所属的专场,该字段+1',
`deal_numbers` int(11) NOT NULL DEFAULT '0' COMMENT '//成交时判断当前商品所属的专场,该字段+1',
`all_money` int(50) NOT NULL DEFAULT '0' COMMENT '//成交时判断当前商品所属的专场,该字段加上成交金额',
`old_status` int(10) NOT NULL DEFAULT '0' COMMENT '//复杂需求恢复原状态,记录原状态值',
PRIMARY KEY (`id`),
KEY `index_id` (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='专场表';
查询语句如下
SELECT
id,`name`,start_time,expires_time,`status`,goods_ids
FROM
mt_specialise
WHERE
STATUS IN (1, 2, 3)
ORDER BY
field(`status`, 2, 1, 3),
CASE
WHEN `status` = 2 THEN
expires_time
END,
CASE
WHEN `status` = 1 THEN
start_time
END,
CASE
WHEN `status` = 3 THEN
expires_time
END DESC
LIMIT 15

Comment list( 0 )

You need to Sign in for post a comment

Help Search

183227_9af5e6a8_1826025 111910_4d91f001_1826025