1、查看所有事件
show events
2、 查看事件状态
SHOW VARIABLES LIKE 'event_scheduler'
ON 事件开启
OFF 事件停止
3、更改事件状态
SET GLOBAL event_scheduler =1;
SET GLOBAL event_scheduler =ON;
4、创建事件
BEGIN
-- 售票数据结果表推送
-- 当日数据归集
DELETE FROM ylxs_dp.ylxs_jqpwfx_mpcsqs WHERE SUBSTR(sj,1,10) = (SUBSTR(NOW(),1,10)) and sjlx ='3';
INSERT INTO ylxs_dp.ylxs_jqpwfx_mpcsqs (sl,jqmc,sj,jdmc,sjsj,sjlx)
SELECT
count(0) as sl,
'玉龙雪山' as jqmc,
orderTime as sj,
scenicAreaName as jdmc,
NOW() as sjsj,
3 as sjlx
FROM
ylxs_hldc.t_dm_ticket_data
WHERE
scenicAreaName in ('冰川公园索道','云杉坪索道','印象丽江','牦牛坪索道','玉龙雪山景区门票')
and SUBSTR(orderTime,1,10) = (SUBSTR(NOW(),1,10))
GROUP BY scenicAreaName,SUBSTR(orderTime,1,13);
INSERT INTO ylxs_dp.ylxs_jqpwfx_mpcsqs (sl,jqmc,sj,jdmc,sjsj,sjlx)
SELECT
count(0) as sl,
'玉龙雪山' as jqmc,
orderTime as sj,
'玉龙雪山景区门票' as jdmc,
NOW() as sjsj,
3 as sjlx
FROM
ylxs_hldc.t_dm_ticket_data
WHERE
scenicAreaName in ('冰川公园索道','云杉坪索道','印象丽江','牦牛坪索道','玉龙雪山景区门票')
and SUBSTR(orderTime,1,10) = (SUBSTR(NOW(),1,10))
GROUP BY SUBSTR(orderTime,1,13);
-- 当月数据归集
DELETE FROM ylxs_dp.ylxs_jqpwfx_mpcsqs WHERE SUBSTR(sj,1,7) = (SUBSTR(NOW(),1,7)) and sjlx ='2';
INSERT INTO ylxs_dp.ylxs_jqpwfx_mpcsqs (sl,jqmc,sj,jdmc,sjsj,sjlx)
SELECT
count(0) as sl,
'玉龙雪山' as jqmc,
orderTime as sj,
scenicAreaName as jdmc,
NOW() as sjsj,
2 as sjlx
FROM
ylxs_hldc.t_dm_ticket_data
WHERE
scenicAreaName in ('冰川公园索道','云杉坪索道','印象丽江','牦牛坪索道','玉龙雪山景区门票')
and SUBSTR(orderTime,1,7) = (SUBSTR(NOW(),1,7))
GROUP BY scenicAreaName,SUBSTR(orderTime,1,10);
INSERT INTO ylxs_dp.ylxs_jqpwfx_mpcsqs (sl,jqmc,sj,jdmc,sjsj,sjlx)
SELECT
count(0) as sl,
'玉龙雪山' as jqmc,
orderTime as sj,
'玉龙雪山景区门票' as jdmc,
NOW() as sjsj,
2 as sjlx
FROM
ylxs_hldc.t_dm_ticket_data
WHERE
scenicAreaName in ('冰川公园索道','云杉坪索道','印象丽江','牦牛坪索道','玉龙雪山景区门票')
and SUBSTR(orderTime,1,7) = (SUBSTR(NOW(),1,7))
GROUP BY SUBSTR(orderTime,1,10);
-- 本年数据归集
DELETE FROM ylxs_dp.ylxs_jqpwfx_mpcsqs WHERE SUBSTR(sj,1,4) = (SUBSTR(NOW(),1,4)) and sjlx ='1';
INSERT INTO ylxs_dp.ylxs_jqpwfx_mpcsqs (sl,jqmc,sj,jdmc,sjsj,sjlx)
SELECT
count(0) as sl,
'玉龙雪山' as jqmc,
orderTime as sj,
scenicAreaName as jdmc,
NOW() as sjsj,
1 as sjlx
FROM
ylxs_hldc.t_dm_ticket_data
WHERE
scenicAreaName in ('冰川公园索道','云杉坪索道','印象丽江','牦牛坪索道','玉龙雪山景区门票')
and SUBSTR(orderTime,1,4) = (SUBSTR(NOW(),1,4))
GROUP BY scenicAreaName,SUBSTR(orderTime,1,7);
INSERT INTO ylxs_dp.ylxs_jqpwfx_mpcsqs (sl,jqmc,sj,jdmc,sjsj,sjlx)
SELECT
count(0) as sl,
'玉龙雪山' as jqmc,
orderTime as sj,
'玉龙雪山景区门票' as jdmc,
NOW() as sjsj,
1 as sjlx
FROM
ylxs_hldc.t_dm_ticket_data
WHERE
scenicAreaName in ('冰川公园索道','云杉坪索道','印象丽江','牦牛坪索道','玉龙雪山景区门票')
and SUBSTR(orderTime,1,4) = (SUBSTR(NOW(),1,4))
GROUP BY SUBSTR(orderTime,1,7);
END