网站制作完工验收单,怎样制作游戏软件,卫生间装修效果图,网站建设仟金手指六六14园区能源管理系统是专注于园区能耗在线监测以及能耗评估的综合能源管理系统。一、应用背景存在问题#xff1a;园区建筑功能构成较为复杂#xff0c;用能设备多#xff0c;耗能量大#xff0c;监管难度高。安全、舒适是首要考虑因素#xff0c;维持这一效果需要消耗更多的…园区能源管理系统是专注于园区能耗在线监测以及能耗评估的综合能源管理系统。一、应用背景存在问题园区建筑功能构成较为复杂用能设备多耗能量大监管难度高。安全、舒适是首要考虑因素维持这一效果需要消耗更多的能源。商业盈利性一定程度影响管理方和消费者缺乏主动节能动力和行为节能意识。痛点需求能耗浪费现象严重存在不用白不用的观念空调温度偏低电梯空载运行等。用能监管较为粗放用能管理模式单一难以及时发现运行中不合理用能。缺少对重要耗能设备(如暖通空调)运行模式与用能效率的监管和优化能源利用效率偏低二、解决思路建立实时、在线的园区能耗计量监测提供能耗统计分析和日常运行监控功能应用建立园区能耗综合分析体系实施科学量化管理三、功能应用四、系统展示1.能耗监测2.统计报表3.费用管理4.能耗分析5.用户管理6.系统管理#长乐云计算项目 1.能耗分析#能耗统计2.能耗同环比3.电度查询#经营管理1.客户电费查询2.账单设置3.五、部分SQL代码#部门能耗统计报表按部门查看#按部门查看
SELECT * FROM(
SELECT
CASEFNO WHEN 008 THEN -1 ELSE FPID END AS FPID,FGUID AS FID,FNAME AS FNAME,FNO AS FI1,FNO AS FVALUE,FNO AS Field,
CASEWHEN FNO LIKE 008______ THEN 0 ELSE 1 END AS FSHOW,eq AS FOPTYPE
FROMb_dcm_bbjcswh
WHEREFNO LIKE 008% AND FNAME NOT LIKE UPS% ) A
ORDER BY
FI1部门能耗统计报表HSELECT*
FROM(SELECTb.FEID,c.FNO,c.FENAME,b.fd3,d.FC3,SUM(b.FM1) SUM(b.FM2) SUM(b.FM3) SUM(b.FM4) SUM(b.FM5) SUM(b.FM6) SUM(b.FM7) SUM(b.FM8) SUM(b.FM9) SUM(b.FM10) SUM(b.FM11) SUM(b.FM12) SUM(b.FM13) SUM(b.FM14) SUM(b.FM15) SUM(b.FM16) SUM(b.FM17) SUM(b.FM18) SUM(b.FM19) SUM(b.FM20) SUM(b.FM21) SUM(b.FM22) SUM(b.FM23) SUM(b.FM24) AS FZJ,0 AS FDELETED,SUM(b.FM1) AS fm1,SUM(b.FM2) AS fm2,SUM(b.FM3) AS fm3,SUM(b.FM4) AS fm4,SUM(b.FM5) AS fm5,SUM(b.FM6) AS fm6,SUM(b.FM7) AS fm7,SUM(b.FM8) AS fm8,SUM(b.FM9) AS fm9,SUM(b.FM10) AS fm10,SUM(b.FM11) AS fm11,SUM(b.FM12) AS fm12,SUM(b.FM13) AS fm13,SUM(b.FM14) AS fm14,SUM(b.FM15) AS fm15,SUM(b.FM16) AS fm16,SUM(b.FM17) AS fm17,SUM(b.FM18) AS fm18,SUM(b.FM19) AS fm19,SUM(b.FM20) AS fm20,SUM(b.FM21) AS fm21,SUM(b.FM22) AS fm22,SUM(b.FM23) AS fm23,SUM(b.FM24) AS fm24FROM(SELECTfguid,a.FEID,a.time,fwritetime,a.fd3,(CASE a.time WHEN 00 THEN FM1 ELSE 0END) FM1,(CASE a.time WHEN 01 THEN FM1 ELSE 0END
) FM2,
(CASE a.time WHEN 02 THEN FM1 ELSE 0
END
) FM3,
(CASE a.time WHEN 03 THEN FM1 ELSE 0
END
) FM4,
(CASE a.time WHEN 04 THEN FM1 ELSE 0
END
) FM5,
(CASE a.time WHEN 05 THEN FM1 ELSE 0
END
) FM6,
(CASE a.time WHEN 06 THEN FM1 ELSE 0
END
) FM7,
(CASE a.time WHEN 07 THEN FM1 ELSE 0
END
) FM8,
(CASE a.time WHEN 08 THEN FM1 ELSE 0
END
) FM9,
(CASE a.time WHEN 09 THEN FM1 ELSE 0
END
) FM10,
(CASE a.time WHEN 10 THEN FM1 ELSE 0
END
) FM11,
(CASE a.time WHEN 11 THEN FM1 ELSE 0
END
) FM12,
(CASE a.time WHEN 12 THEN FM1 ELSE 0
END
) FM13,
(CASE a.time WHEN 13 THEN FM1 ELSE 0
END
) FM14,
(CASE a.time WHEN 14 THEN FM1 ELSE 0
END
) FM15,
(CASE a.time WHEN 15 THEN FM1 ELSE 0
END
) FM16,
(CASE a.time WHEN 16 THEN FM1 ELSE 0
END
) FM17,
(CASE a.time WHEN 17 THEN FM1 ELSE 0
END
) FM18,
(CASE a.time WHEN 18 THEN FM1 ELSE 0
END
) FM19,
(CASE a.time WHEN 19 THEN FM1 ELSE 0
END
) FM20,
(CASE a.time WHEN 20 THEN FM1 ELSE 0
END
) FM21,
(CASE a.time WHEN 21 THEN FM1 ELSE 0
END
) FM22,
(CASE a.time WHEN 22 THEN FM1 ELSE 0
END
) FM23,
(CASE a.time WHEN 23 THEN FM1 ELSE 0
END
) FM24
FROM(SELECTfguid,FEID,FM1,DATE_FORMAT(FWRITETIME, %Y-%m-%d) AS fd3,DATE_FORMAT(FWRITETIME, %H) AS TIME,fwritetimeFROM(SELECT CASE WHENFEID 01280001 THEN 1240099END AS FEID,FWRITETIME,FM1 * 0.4 AS FM1,FGUID
FROMd_nhgl_runloghour
WHEREFEID 01280001
UNION ALL
SELECT CASE WHENFEID 01280001 THEN 1240098
END AS FEID,
FWRITETIME,
FM1 * 0.5 AS FM1,
FGUID
FROMd_nhgl_runloghour
WHEREFEID 01280001
UNION ALL
SELECT CASE WHENFEID 01280001 THEN 1240097
END AS FEID,
FWRITETIME,
FM1 * 0.6 AS FM1,
FGUID
FROMd_nhgl_runloghour
WHEREFEID 01280001
) f
) a
) b,
b_dcm_bbjcswhc c
JOIN b_dcm_bbjcswh d ONc.FC11 d.FGUID
WHEREc.FEID b.FEID AND c.FNO LIKE 008%
GROUP BYb.FEID,b.fd3
) e
ORDER BYe.fd3
DESC,e.feid部门能耗统计报表DSELECT*
FROM(SELECTb.FEID,c.FNO,c.fc3,c.FENAME,c.FLX,b.fd3,SUM(b.FM1) SUM(b.FM2) SUM(b.FM3) SUM(b.FM4) SUM(b.FM5) SUM(b.FM6) SUM(b.FM7) SUM(b.FM8) SUM(b.FM9) SUM(b.FM10) SUM(b.FM11) SUM(b.FM12) SUM(b.FM13) SUM(b.FM14) SUM(b.FM15) SUM(b.FM16) SUM(b.FM17) SUM(b.FM18) SUM(b.FM19) SUM(b.FM20) SUM(b.FM21) SUM(b.FM22) SUM(b.FM23) SUM(b.FM24) SUM(b.FM25) SUM(b.FM26) SUM(b.FM27) SUM(b.FM28) SUM(b.FM29) SUM(b.FM30) SUM(b.FM31) AS FZJ,0 AS FDELETED,SUM(b.FM1) AS fm1,SUM(b.FM2) AS fm2,SUM(b.FM3) AS fm3,SUM(b.FM4) AS fm4,SUM(b.FM5) AS fm5,SUM(b.FM6) AS fm6,SUM(b.FM7) AS fm7,SUM(b.FM8) AS fm8,SUM(b.FM9) AS fm9,SUM(b.FM10) AS fm10,SUM(b.FM11) AS fm11,SUM(b.FM12) AS fm12,SUM(b.FM13) AS fm13,SUM(b.FM14) AS fm14,SUM(b.FM15) AS fm15,SUM(b.FM16) AS fm16,SUM(b.FM17) AS fm17,SUM(b.FM18) AS fm18,SUM(b.FM19) AS fm19,SUM(b.FM20) AS fm20,SUM(b.FM21) AS fm21,SUM(b.FM22) AS fm22,SUM(b.FM23) AS fm23,SUM(b.FM24) AS fm24,SUM(b.FM25) AS fm25,SUM(b.FM26) AS fm26,SUM(b.FM27) AS fm27,SUM(b.FM28) AS fm28,SUM(b.FM29) AS fm29,SUM(b.FM30) AS fm30,SUM(b.FM31) AS fm31FROM(SELECTfguid,a.FEID,a.time,fwritetime,a.fd3,(CASE a.time WHEN 31 THEN FM1 ELSE 0END) FM31,(CASE a.time WHEN 1 THEN FM1 ELSE 0END
) FM1,
(CASE a.time WHEN 2 THEN FM1 ELSE 0
END
) FM2,
(CASE a.time WHEN 3 THEN FM1 ELSE 0
END
) FM3,
(CASE a.time WHEN 4 THEN FM1 ELSE 0
END
) FM4,
(CASE a.time WHEN 5 THEN FM1 ELSE 0
END
) FM5,
(CASE a.time WHEN 6 THEN FM1 ELSE 0
END
) FM6,
(CASE a.time WHEN 7 THEN FM1 ELSE 0
END
) FM7,
(CASE a.time WHEN 8 THEN FM1 ELSE 0
END
) FM8,
(CASE a.time WHEN 9 THEN FM1 ELSE 0
END
) FM9,
(CASE a.time WHEN 10 THEN FM1 ELSE 0
END
) FM10,
(CASE a.time WHEN 11 THEN FM1 ELSE 0
END
) FM11,
(CASE a.time WHEN 12 THEN FM1 ELSE 0
END
) FM12,
(CASE a.time WHEN 13 THEN FM1 ELSE 0
END
) FM13,
(CASE a.time WHEN 14 THEN FM1 ELSE 0
END
) FM14,
(CASE a.time WHEN 15 THEN FM1 ELSE 0
END
) FM15,
(CASE a.time WHEN 16 THEN FM1 ELSE 0
END
) FM16,
(CASE a.time WHEN 17 THEN FM1 ELSE 0
END
) FM17,
(CASE a.time WHEN 18 THEN FM1 ELSE 0
END
) FM18,
(CASE a.time WHEN 19 THEN FM1 ELSE 0
END
) FM19,
(CASE a.time WHEN 20 THEN FM1 ELSE 0
END
) FM20,
(CASE a.time WHEN 21 THEN FM1 ELSE 0
END
) FM21,
(CASE a.time WHEN 22 THEN FM1 ELSE 0
END
) FM22,
(CASE a.time WHEN 23 THEN FM1 ELSE 0
END
) FM23,
(CASE a.time WHEN 24 THEN FM1 ELSE 0
END
) FM24,
(CASE a.time WHEN 25 THEN FM1 ELSE 0
END
) FM25,
(CASE a.time WHEN 26 THEN FM1 ELSE 0
END
) FM26,
(CASE a.time WHEN 27 THEN FM1 ELSE 0
END
) FM27,
(CASE a.time WHEN 28 THEN FM1 ELSE 0
END
) FM28,
(CASE a.time WHEN 29 THEN FM1 ELSE 0
END
) FM29,
(CASE a.time WHEN 30 THEN FM1 ELSE 0
END
) FM30
FROM(SELECTFGUID,FEID,FM1,DATE_FORMAT(FWRITETIME, %Y-%m) AS fd3,DATE_FORMAT(FWRITETIME, %d) AS TIME,FWRITETIMEFROM(SELECT CASE WHENFEID 01280001 THEN 1240099END AS FEID,FWRITETIME,FM1 * 0.4 AS FM1,FGUID
FROMd_nhgl_runloghour
WHEREFEID 01280001
UNION ALL
SELECT CASE WHENFEID 01280001 THEN 1240098
END AS FEID,
FWRITETIME,
FM1 * 0.5 AS FM1,
FGUID
FROMd_nhgl_runloghour
WHEREFEID 01280001
UNION ALL
SELECT CASE WHENFEID 01280001 THEN 1240097
END AS FEID,
FWRITETIME,
FM1 * 0.6 AS FM1,
FGUID
FROMd_nhgl_runloghour
WHEREFEID 01280001
) f
) a
) b,
b_dcm_bbjcswhc c
JOIN b_dcm_bbjcswh d ONc.FC11 d.FGUID
WHEREc.FEID b.FEID AND c.FNO LIKE 008%
GROUP BYb.FEID,b.fd3
) e
ORDER BYe.fd3
DESC#能耗同环比SELECTFEID,FENAME,FNO, FD1, FD3,FD2, FM1,FORMAT(0.7979 * FM1, 2) AS Zhtpf,FORMAT(0.1229 * FM1, 2) AS Zhbzm,FM2, FD4,FM2 - FM1 AS Hbzzl,FORMAT((FM1 - FM2) / FM1,2) AS FM4,FM3, FD5,FM3 - FM1 AS Tbzzl,FORMAT((FM3 - FM1) / FM3,2) AS FM5
FROM(SELECTa.FEID,a.FENAME,a.FNO,DATE_FORMAT(a.FWRITETIME, %Y-%m) AS FD3,DATE_FORMAT(a.FWRITETIME, %m) AS FD1,DATE_FORMAT(a.FWRITETIME, %Y) AS FD2,DATE_FORMAT(c.FWRITETIME, %Y-%m) AS FD4,DATE_FORMAT(d.FWRITETIME, %Y-%m) AS FD5,(b.FM1 - a.FM1) AS FM1,(a.FM1 - c.FM1) AS FM2,(e.FM1 - d.FM1) AS FM3FROM(SELECTa.feid,g.FENAME,g.FNO,a.fwritetime,a.fm1,a.fguidFROMd_nhgl_test a,b_dcm_bbjcswhc gJOIN b_dcm_bbjcswh f ONg.FC11 f.FGUIDWHEREa.feid g.feid) a
JOIN d_nhgl_test b ONa.feid b.feid AND a.FWRITETIME DATE_SUB(b.FWRITETIME,INTERVAL 1 MONTH)
JOIN d_nhgl_test c ONa.feid c.feid AND a.FWRITETIME DATE_ADD(c.FWRITETIME,INTERVAL 1 MONTH)
JOIN d_nhgl_test d ONa.feid d.feid AND a.FWRITETIME DATE_ADD(d.FWRITETIME,INTERVAL 12 MONTH)
JOIN d_nhgl_test e ONa.feid e.feid AND a.FWRITETIME DATE_ADD(e.FWRITETIME,INTERVAL 11 MONTH)
) F#部门费用统计按部门查看SELECT*
FROM(SELECTCASE FNO WHEN 008 THEN -1ELSE FPID END AS FPID,FGUID AS FID,FNAME AS FNAME,FNO AS FI1,FNO AS FVALUE,FNO AS Field,CASE WHEN FNO LIKE 008______ THEN 0 ELSE 1 END AS FSHOW,eq AS FOPTYPEFROMb_dcm_bbjcswhWHEREFNO LIKE 008% ) A ORDER BY FI1部门费用统计报表SELECT FEID,FNO,FENAME,FD3,FC3,0.9*FZJ AS FYY,FDELETED FROM (SELECTb.FEID,c.FNO,c.FENAME,b.fd3,d.FC3,SUM(b.FM1) SUM(b.FM2) SUM(b.FM3) SUM(b.FM4) SUM(b.FM5) SUM(b.FM6) SUM(b.FM7) SUM(b.FM8) SUM(b.FM9) SUM(b.FM10) SUM(b.FM11) SUM(b.FM12) SUM(b.FM13) SUM(b.FM14) SUM(b.FM15) SUM(b.FM16) SUM(b.FM17) SUM(b.FM18) SUM(b.FM19) SUM(b.FM20) SUM(b.FM21) SUM(b.FM22) SUM(b.FM23) SUM(b.FM24) AS FZJ,0 AS FDELETED,SUM(b.FM1) AS fm1,SUM(b.FM2) AS fm2,SUM(b.FM3) AS fm3,SUM(b.FM4) AS fm4,SUM(b.FM5) AS fm5,SUM(b.FM6) AS fm6,SUM(b.FM7) AS fm7,SUM(b.FM8) AS fm8,SUM(b.FM9) AS fm9,SUM(b.FM10) AS fm10,SUM(b.FM11) AS fm11,SUM(b.FM12) AS fm12,SUM(b.FM13) AS fm13,SUM(b.FM14) AS fm14,SUM(b.FM15) AS fm15,SUM(b.FM16) AS fm16,SUM(b.FM17) AS fm17,SUM(b.FM18) AS fm18,SUM(b.FM19) AS fm19,SUM(b.FM20) AS fm20,SUM(b.FM21) AS fm21,SUM(b.FM22) AS fm22,SUM(b.FM23) AS fm23,SUM(b.FM24) AS fm24
FROM(SELECTfguid,a.FEID,a.time,fwritetime,a.fd3,(CASE a.time WHEN 00 THEN FM1 ELSE 0END
) FM1,
(CASE a.time WHEN 01 THEN FM1 ELSE 0
END
) FM2,
(CASE a.time WHEN 02 THEN FM1 ELSE 0
END
) FM3,
(CASE a.time WHEN 03 THEN FM1 ELSE 0
END
) FM4,
(CASE a.time WHEN 04 THEN FM1 ELSE 0
END
) FM5,
(CASE a.time WHEN 05 THEN FM1 ELSE 0
END
) FM6,
(CASE a.time WHEN 06 THEN FM1 ELSE 0
END
) FM7,
(CASE a.time WHEN 07 THEN FM1 ELSE 0
END
) FM8,
(CASE a.time WHEN 08 THEN FM1 ELSE 0
END
) FM9,
(CASE a.time WHEN 09 THEN FM1 ELSE 0
END
) FM10,
(CASE a.time WHEN 10 THEN FM1 ELSE 0
END
) FM11,
(CASE a.time WHEN 11 THEN FM1 ELSE 0
END
) FM12,
(CASE a.time WHEN 12 THEN FM1 ELSE 0
END
) FM13,
(CASE a.time WHEN 13 THEN FM1 ELSE 0
END
) FM14,
(CASE a.time WHEN 14 THEN FM1 ELSE 0
END
) FM15,
(CASE a.time WHEN 15 THEN FM1 ELSE 0
END
) FM16,
(CASE a.time WHEN 16 THEN FM1 ELSE 0
END
) FM17,
(CASE a.time WHEN 17 THEN FM1 ELSE 0
END
) FM18,
(CASE a.time WHEN 18 THEN FM1 ELSE 0
END
) FM19,
(CASE a.time WHEN 19 THEN FM1 ELSE 0
END
) FM20,
(CASE a.time WHEN 20 THEN FM1 ELSE 0
END
) FM21,
(CASE a.time WHEN 21 THEN FM1 ELSE 0
END
) FM22,
(CASE a.time WHEN 22 THEN FM1 ELSE 0
END
) FM23,
(CASE a.time WHEN 23 THEN FM1 ELSE 0
END
) FM24
FROM(SELECTfguid,FEID,FM1,DATE_FORMAT(FWRITETIME, %Y-%m-%d) AS fd3,DATE_FORMAT(FWRITETIME, %H) AS TIME,fwritetimeFROM(SELECT CASE WHEN FEID 01280001 THEN 1240099 END AS FEID,FWRITETIME, FM1*0.4 AS FM1,FGUID FROM d_nhgl_runloghour WHERE FEID01280001
UNION ALL
SELECT CASE WHEN FEID 01280001 THEN 1240098 END AS FEID,FWRITETIME, FM1*0.5 AS FM1,FGUID FROM d_nhgl_runloghour WHERE FEID01280001
UNION ALL
SELECT CASE WHEN FEID 01280001 THEN 1240097 END AS FEID,FWRITETIME, FM1*0.6 AS FM1,FGUID FROM d_nhgl_runloghour WHERE FEID01280001)f) a
) b
,b_dcm_bbjcswhc c JOIN b_dcm_bbjcswh d ON c.FC11d.FGUID
WHEREc.FEID b.FEID AND c.FNO LIKE 008%
GROUP BYb.FEID,b.fd3)e ORDER BY e.fd3 desc ,e.feid账单查询SELECTa.FEID,a.FENAME,FKH,FKNAME,CONCAT(a.fwritetime, ~, b.FWRITETIME) AS FDATE,ROUND(a.fm1 0.3 * b.FM1) AS FZFY,ROUND(a.fm1) AS FM1,ROUND(a.fm1) AS FDF,ROUND(0.6 * a.fm1) AS FM2,ROUND(0.3 * a.FM1) AS FSF,ROUND(a.fm1) AS FGTM1,ROUND(a.fm1) AS FGTDF,ROUND(0.6 * a.fm1) AS FGTM2,ROUND(0.3 * a.FM1) AS FGTSF,2 * ROUND(a.fm1) AS FHHM1,2 * ROUND(a.fm1) AS FHJDF,2 * ROUND(0.6 * a.fm1) AS FHJM2,2 * ROUND(0.3 * a.FM1) AS FHJSF,DATE_ADD(a.FWRITETIME,INTERVAL(d.FC6 - d.FC4) DAY) AS FZHJN,DATE_ADD(b.FWRITETIME, INTERVAL 1 DAY) AS FZD
FROM(SELECTa.feid,g.FENAME,g.FNO,g.FKNAME,g.FKH,DATE_FORMAT(a.FWRITETIME, %Y-%m-%d) AS fwritetime,a.fm1,a.fguidFROMd_nhgl_runlogday a,b_dcm_bbjcswhc gJOIN b_dcm_bbjcswh f ONg.FC11 f.FGUID AND g.FNO LIKE 004%WHEREa.feid g.feid
) a
JOIN(SELECTfeid,FM1,DATE_FORMAT(FWRITETIME, %Y-%m-%d) AS FWRITETIMEFROMd_nhgl_runlogday
) b
ONa.feid b.feid AND a.FWRITETIME DATE_ADD((DATE_SUB(b.FWRITETIME,INTERVAL 1 MONTH)),INTERVAL 1 DAY)
JOIN(
SELECT*
FROMd_nhgl_zdsz
) d
ONDATE_FORMAT(a.FWRITETIME, %d) FC1账单存储过程CREATE PROCEDURE billsave()
BEGININSERT INTO f_nhgl_zdcc
SELECTa.FEID, a.FENAME, FKH, FKNAME, CONCAT(a.fwritetime, ~, b.FWRITETIME) AS FDATE,ROUND(a.fm1 0.3 * b.FM1) AS FZFY,ROUND(a.fm1) AS FM1,ROUND(a.fm1) AS FDF,ROUND(0.6 * a.fm1) AS FM2,ROUND(0.3 * a.FM1) AS FSF,ROUND(a.fm1) AS FGTM1,ROUND(a.fm1) AS FGTDF,ROUND(0.6 * a.fm1) AS FGTM2,ROUND(0.3 * a.FM1) AS FGTSF,2 * ROUND(a.fm1) AS FHHM1,2 * ROUND(a.fm1) AS FHJDF,2 * ROUND(0.6 * a.fm1) AS FHJM2,2 * ROUND(0.3 * a.FM1) AS FHJSF,DATE_ADD(a.FWRITETIME,INTERVAL(d.FC6 - d.FC4) DAY) AS FZHJN,DATE_ADD(b.FWRITETIME, INTERVAL 1 DAY) AS FZD
FROM(SELECTa.feid,g.FENAME,g.FNO,g.FKNAME,g.FKH,DATE_FORMAT(a.FWRITETIME, %Y-%m-%d) AS fwritetime,a.fm1,a.fguidFROMd_nhgl_runlogday a,b_dcm_bbjcswhc gJOIN b_dcm_bbjcswh f ONg.FC11 f.FGUID AND g.FNO LIKE 004%WHEREa.feid g.feid
) a
JOIN(SELECTfeid,FM1,DATE_FORMAT(FWRITETIME, %Y-%m-%d) AS FWRITETIMEFROMd_nhgl_runlogday
) b
ONa.feid b.feid AND a.FWRITETIME DATE_ADD((DATE_SUB(b.FWRITETIME,INTERVAL 1 MONTH)),INTERVAL 1 DAY)
JOIN(
SELECT*
FROMd_nhgl_zdsz
) d
ONDATE_FORMAT(a.FWRITETIME, %d) FC1;
END账单存储过程触发器*/创建mysql的定时器event*/
create event if not exists eventJob
on schedule every 1 second
on completion PRESERVE
do call billsave2;账单存储过程定时器至此所有的准备工作已经写完了做完这些mysql要想利用定时器必须的做准备工作就是把mysql的定时器给开启了
SET GLOBAL event_scheduler 1; -- 启动定时器
SET GLOBAL event_scheduler 0; -- 停止定时器账单存储过程事件启动紧接着还要开启事件
ALTER EVENT eventJob ON COMPLETION PRESERVE ENABLE; -- 开启事件
ALTER EVENT eventJob ON COMPLETION PRESERVE DISABLE; -- 关闭事件每月几号定时启动CREATE DEFINER gzy% EVENT delLastMonth ON SCHEDULE EVERY1 MONTH STARTS 2016-08-01 00:00:05
ON COMPLETION NOT PRESERVE ENABLE
DO
CALLdelLastMonth#系统管理采集设备维护SELECT3ec955d5-e9d1-11e8-bd99-005056ba54a4 AS FID,-1 AS FPID,设备类型 AS FNAME,设备类型 AS FVLAUE,FC5 AS FIELD,0 AS MCHOICE
UNION ALL
SELECTUUID() AS FID, 3ec955d5-e9d1-11e8-bd99-005056ba54a4 AS FPID, A.optionValue AS FNAME, A.optionName AS FVLAUE, FC5 AS FIELD,1 AS MCHOICE
FROM(SELECToptionName,optionValueFROMsys_enumsWHEREgroupName 设备类型
) A
UNION ALL
SELECTf3e7b5eb-e9d6-11e8-bd99-005056ba54a4 AS FID,-1 AS FPID,型号规格 AS FNAME,型号规格 AS FVLAUE,FXHGG AS FIELD,0 AS MCHOICE
UNION ALL
SELECTUUID() AS FID, f3e7b5eb-e9d6-11e8-bd99-005056ba54a4 AS FPID, B.optionValue AS FNAME, B.optionName AS FVLAUE, FXHGG AS FIELD,0 AS MCHOICE
FROM(SELECToptionName,optionValueFROMsys_enumsWHEREgroupName 型号规格
) B
UNION ALL
SELECT6b8cd459-e9d8-11e8-bd99-005056ba54a4 AS FID,-1 AS FPID,安装区域 AS FNAME,安装区域 AS FVLAUE,FAZQY AS FIELD,0 AS MCHOICE
UNION ALL
SELECTUUID() AS FID, 6b8cd459-e9d8-11e8-bd99-005056ba54a4 AS FPID, C.optionValue AS FNAME, C.optionName AS FVLAUE, FAZQY AS FIELD,0 AS MCHOICE
FROM(SELECToptionName,optionValueFROMsys_enums WHEREgroupName 安装区域
) C ORDER BY FVLAUE六、解决方案PPT
