67 lines
2.4 KiB
SQL
67 lines
2.4 KiB
SQL
CREATE TABLE IF NOT EXISTS `task_center_condition_jump_config` (
|
||
`id` bigint NOT NULL COMMENT '主键ID',
|
||
`config_id` bigint NOT NULL COMMENT '任务中心配置ID',
|
||
`sys_origin` varchar(32) NOT NULL COMMENT '来源系统',
|
||
`task_category` varchar(32) NOT NULL COMMENT '任务分类：DAILY/NEWBIE',
|
||
`condition_type` varchar(64) NOT NULL COMMENT '完成条件类型',
|
||
`jump_type` varchar(64) NOT NULL DEFAULT 'NONE' COMMENT '跳转类型',
|
||
`jump_page` varchar(512) NOT NULL DEFAULT '' COMMENT '跳转参数',
|
||
`sort_order` int NOT NULL DEFAULT '0' COMMENT '排序',
|
||
`create_time` datetime(3) DEFAULT NULL COMMENT '创建时间',
|
||
`update_time` datetime(3) DEFAULT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_task_center_condition_jump` (`sys_origin`, `task_category`, `condition_type`),
|
||
KEY `idx_task_center_condition_jump_config_id` (`config_id`),
|
||
KEY `idx_task_center_condition_jump_sys_category` (`sys_origin`, `task_category`),
|
||
KEY `idx_task_center_condition_jump_condition` (`condition_type`),
|
||
KEY `idx_task_center_condition_jump_sort` (`sort_order`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='任务中心条件跳转配置';
|
||
|
||
INSERT INTO task_center_condition_jump_config (
|
||
id,
|
||
config_id,
|
||
sys_origin,
|
||
task_category,
|
||
condition_type,
|
||
jump_type,
|
||
jump_page,
|
||
sort_order,
|
||
create_time,
|
||
update_time
|
||
)
|
||
SELECT
|
||
id,
|
||
config_id,
|
||
sys_origin,
|
||
task_category,
|
||
condition_type,
|
||
CASE
|
||
WHEN condition_type = 'RECHARGE_GOLD' THEN 'RECHARGE_IN_APP'
|
||
ELSE first_jump_type
|
||
END AS jump_type,
|
||
first_jump_page AS jump_page,
|
||
sort_order,
|
||
create_time,
|
||
update_time
|
||
FROM (
|
||
SELECT
|
||
MIN(id) AS id,
|
||
config_id,
|
||
sys_origin,
|
||
task_category,
|
||
condition_type,
|
||
COALESCE(NULLIF(SUBSTRING_INDEX(GROUP_CONCAT(jump_type ORDER BY sort_order ASC, id ASC SEPARATOR '\n'), '\n', 1), ''), 'ROOM_RANDOM_WITH_MIC') AS first_jump_type,
|
||
COALESCE(SUBSTRING_INDEX(GROUP_CONCAT(jump_page ORDER BY sort_order ASC, id ASC SEPARATOR '\n'), '\n', 1), '') AS first_jump_page,
|
||
MIN(sort_order) AS sort_order,
|
||
COALESCE(MIN(create_time), NOW(3)) AS create_time,
|
||
NOW(3) AS update_time
|
||
FROM task_center_task_config
|
||
WHERE condition_type <> ''
|
||
GROUP BY config_id, sys_origin, task_category, condition_type
|
||
) source
|
||
ON DUPLICATE KEY UPDATE
|
||
jump_type = VALUES(jump_type),
|
||
jump_page = VALUES(jump_page),
|
||
sort_order = VALUES(sort_order),
|
||
update_time = VALUES(update_time);
|