内容简介:存储过程调用方式:CALL Pro_Get_CO2('2018','','','');
BEGIN /* 存储过程名称:Pro_Get_Carbon_Climate 存储过程功能说明:不同气候区碳排放占比 创建日期: 涉及的表或试图: 表或试图名称 用途 存储过程调用: 存储过程名称 用途 NONE 参数说明: 传入参数: 参数名 说明 ivF_Year 年度 返回字段说明: 调用示例: CALL Pro_Get_Carbon_Climate(ivF_Year); CALL Pro_Get_Carbon_Climate('2018'); */ declare SQL1 varchar(5000); declare SQL2 varchar(5000); declare SQLTotal varchar(5000); declare SQLQuery varchar(5000); -- 一次能源碳排放总量 SET SQL1='select t1.F_ID, t1.F_OrgName, t1.F_ProvinceCode, t1.F_CityCode, t1.F_Climate, t1.F_OrgType, t1.F_BuildArea, t1.F_EnergyPerson, t2.F_Year, sum(t2.F_Value*t3.F_NCV*t3.F_EF) as TPF_HL,sum(t2.F_Value*t3.F_Formular) as ZBM FROM t_organization t1 join t_energy_data t2 ON t1.F_ID = t2.F_OrgID join v_zjpflist t3 on t2.F_FieldID=t3.F_FieldID where t3.F_IsSum=1 '; SET SQL1 = CONCAT(SQL1,' and t2.F_Year= ', ivF_Year); -- 二次能源碳排放总量 SET SQL2='select t1.F_ID, t2.F_Year, sum(CASE WHEN t3.F_CalcType = 2 THEN(t2.F_Value * t3.F_EF)END ) as TPF_DL, sum(CASE WHEN t3.F_CalcType = 3 THEN(t2.F_Value * t3.F_EF) END ) as TPF_RL, sum(t2.F_Value*t3.F_Formular) as ZBM FROM t_organization t1 join t_energy_data t2 ON t1.F_ID = t2.F_OrgID join v_jjpflist t3 on t2.F_FieldID=t3.F_FieldID AND t1.F_ID =T3.F_OrgID where t3.F_IsSum=1 '; SET SQL2 = CONCAT(SQL2,' and t2.F_Year=', ivF_Year); SET SQL1 = CONCAT(SQL1,' group by t1.F_ID, t1.F_OrgName, t1.F_ProvinceCode, t1.F_CityCode, t1.F_Climate, t1.F_OrgType, t1.F_BuildArea, t1.F_EnergyPerson, t2.F_Year '); SET SQL2 = CONCAT(SQL2,' group by t1.F_ID, t2.F_Year '); #SELECT SQL1;# 输出拼接 sql 语句,相当于print #SELECT SQL2;# 输出拼接sql语句,相当于print SET SQLTotal='select AA.F_Climate, SUM(AA.F_BuildArea) AS F_BuildArea, SUM(AA.F_EnergyPerson) AS F_EnergyPerson, SUM(AA.ZBM + BB.ZBM) AS F_TotalZBM, SUM(AA.TPF_HL + BB.TPF_DL + BB.TPF_RL) AS F_TotalTPF, SUM(AA.TPF_HL) AS TPF_HL, SUM(BB.TPF_DL) AS TPF_DL, SUM(BB.TPF_RL) AS TPF_RL '; SET SQLTotal = CONCAT(SQLTotal,' from (', SQL1,') AA JOIN (',SQL2,') BB ON AA.F_ID=BB.F_ID AND AA.F_Year=BB.F_Year '); SET SQLTotal = CONCAT(SQLTotal,' GROUP BY AA.F_Climate '); #SELECT SQLTotal; SET SQLQuery ='select BBB.F_Climate, BBB.F_Name, AAA.F_TotalTPF '; SET SQLQuery = CONCAT(SQLQuery,' from (select a.F_Code as F_Climate ,a.F_Name,a.F_Order from t_dt_datadict a where a.F_DictType=2) BBB LEFT JOIN (',SQLTotal,') AAA ON AAA.F_Climate=BBB.F_Climate '); SET SQLQuery = CONCAT(SQLQuery,' ORDER BY BBB.F_Order '); #SELECT SQLQuery; SET @SQLQuery = SQLQuery; prepare s1 from @SQLQuery; EXECUTE s1 ; deallocate prepare s1 ; END
BEGIN /* 存储过程名称:Pro_Get_CO2 存储过程功能说明:不同类型机构碳排放占比 创建日期: 涉及的表或试图: 表或试图名称 用途 存储过程调用: 存储过程名称 用途 NONE 参数说明: 传入参数: 参数名 说明 ivF_Year 年度 返回字段说明: 调用示例: CALL Pro_Get_Carbon_OrgType(ivF_Year); CALL Pro_Get_Carbon_OrgType('2018'); */ declare SQL1 varchar(5000); declare SQL2 varchar(5000); declare SQLTotal varchar(5000); declare SQLQuery varchar(5000); -- 一次能源碳排放总量 SET SQL1='select t1.F_ID, t1.F_OrgName, t1.F_ProvinceCode, t1.F_CityCode, t1.F_Climate, t1.F_OrgType, t1.F_BuildArea, t1.F_EnergyPerson, t2.F_Year, sum(t2.F_Value*t3.F_NCV*t3.F_EF) as TPF_HL,sum(t2.F_Value*t3.F_Formular) as ZBM FROM t_organization t1 join t_energy_data t2 ON t1.F_ID = t2.F_OrgID join v_zjpflist t3 on t2.F_FieldID=t3.F_FieldID where t3.F_IsSum=1 '; SET SQL1 = CONCAT(SQL1,' and t2.F_Year= ', ivF_Year); -- 二次能源碳排放总量 SET SQL2='select t1.F_ID, t2.F_Year, sum(CASE WHEN t3.F_CalcType = 2 THEN(t2.F_Value * t3.F_EF)END ) as TPF_DL, sum(CASE WHEN t3.F_CalcType = 3 THEN(t2.F_Value * t3.F_EF) END ) as TPF_RL, sum(t2.F_Value*t3.F_Formular) as ZBM FROM t_organization t1 join t_energy_data t2 ON t1.F_ID = t2.F_OrgID join v_jjpflist t3 on t2.F_FieldID=t3.F_FieldID AND t1.F_ID =T3.F_OrgID where t3.F_IsSum=1 '; SET SQL2 = CONCAT(SQL2,' and t2.F_Year=', ivF_Year); SET SQL1 = CONCAT(SQL1,' group by t1.F_ID, t1.F_OrgName, t1.F_ProvinceCode, t1.F_CityCode, t1.F_Climate, t1.F_OrgType, t1.F_BuildArea, t1.F_EnergyPerson, t2.F_Year '); SET SQL2 = CONCAT(SQL2,' group by t1.F_ID, t2.F_Year '); #SELECT SQL1;# 输出拼接sql语句,相当于print #SELECT SQL2;# 输出拼接sql语句,相当于print SET SQLTotal='select AA.F_OrgType, SUM(AA.F_BuildArea) AS F_BuildArea, SUM(AA.F_EnergyPerson) AS F_EnergyPerson, SUM(AA.ZBM + BB.ZBM) AS F_TotalZBM, SUM(AA.TPF_HL + BB.TPF_DL + BB.TPF_RL) AS F_TotalTPF, SUM(AA.TPF_HL) AS TPF_HL, SUM(BB.TPF_DL) AS TPF_DL, SUM(BB.TPF_RL) AS TPF_RL '; SET SQLTotal = CONCAT(SQLTotal,' from (', SQL1,') AA JOIN (',SQL2,') BB ON AA.F_ID=BB.F_ID AND AA.F_Year=BB.F_Year '); SET SQLTotal = CONCAT(SQLTotal,' GROUP BY AA.F_OrgType '); #SELECT SQLTotal; SET SQLQuery ='select BBB.F_OrgType, BBB.F_Name, AAA.F_TotalTPF '; SET SQLQuery = CONCAT(SQLQuery,' from (select a.F_Code as F_OrgType ,a.F_Name,a.F_Order from t_dt_datadict a where a.F_DictType=1) BBB LEFT JOIN (',SQLTotal,') AAA ON AAA.F_OrgType=BBB.F_OrgType '); SET SQLQuery = CONCAT(SQLQuery,' ORDER BY BBB.F_Order '); #SELECT SQLQuery; SET @SQLQuery = SQLQuery; prepare s1 from @SQLQuery; EXECUTE s1 ; deallocate prepare s1 ; END
BEGIN /* 存储过程名称:Pro_Get_CO2 存储过程功能说明:获得年度统计分析(碳排放,折标煤) 创建日期: 涉及的表或试图: 表或试图名称 用途 存储过程调用: 存储过程名称 用途 NONE 参数说明: 传入参数: 参数名 说明 F_Year 年度 F_ProvinceCode 地区 F_Climate 气候区 F_OrgType 机构类型 返回字段说明: 调用示例: CALL Pro_Get_CO2(F_Year,F_ProvinceCode,F_Climate,F_OrgType); CALL Pro_Get_CO2('2018','','',''); */ declare SQL1 varchar(5000); declare SQL2 varchar(5000); declare SQLTotal varchar(5000); -- 一次能源碳排放总量 SET SQL1='select t1.F_ID, t1.F_OrgName, t1.F_ProvinceCode, t1.F_CityCode, t1.F_Climate, t1.F_OrgType, t1.F_BuildArea, t1.F_EnergyPerson, t2.F_Year, sum(t2.F_Value*t3.F_NCV*t3.F_EF) as TPF_HL,sum(t2.F_Value*t3.F_Formular) as ZBM FROM t_organization t1 join t_energy_data t2 ON t1.F_ID = t2.F_OrgID join v_zjpflist t3 on t2.F_FieldID=t3.F_FieldID where t3.F_IsSum=1 '; -- 二次能源碳排放总量 SET SQL2='select t1.F_ID, t2.F_Year, sum(CASE WHEN t3.F_CalcType = 2 THEN(t2.F_Value * t3.F_EF)END ) as TPF_DL, sum(CASE WHEN t3.F_CalcType = 3 THEN(t2.F_Value * t3.F_EF) END ) as TPF_RL, sum(t2.F_Value*t3.F_Formular) as ZBM FROM t_organization t1 join t_energy_data t2 ON t1.F_ID = t2.F_OrgID join v_jjpflist t3 on t2.F_FieldID=t3.F_FieldID AND t1.F_ID =T3.F_OrgID where t3.F_IsSum=1 '; IF F_Year IS NOT NULL AND F_Year <> '' THEN SET SQL1 = CONCAT(SQL1,' and t2.F_Year=', F_Year); SET SQL2 = CONCAT(SQL2,' and t2.F_Year=', F_Year); END IF; IF F_ProvinceCode IS NOT NULL AND F_ProvinceCode <> '' THEN SET SQL1 = CONCAT(SQL1,' and t1.F_ProvinceCode=''', F_ProvinceCode,''' '); SET SQL2 = CONCAT(SQL2,' and t1.F_ProvinceCode=''', F_ProvinceCode,''' '); END IF; IF F_Climate IS NOT NULL AND F_Climate <> '' THEN SET SQL1 = CONCAT(SQL1,' and t1.F_Climate=''', F_Climate,''' '); SET SQL2 = CONCAT(SQL2,' and t1.F_Climate=''', F_Climate,''' '); END IF; IF F_OrgType IS NOT NULL AND F_OrgType <> '' THEN SET SQL1 = CONCAT(SQL1,' and t1.F_OrgType=''', F_OrgType,''' '); SET SQL2 = CONCAT(SQL2,' and t1.F_OrgType=''', F_OrgType,''' '); END IF; SET SQL1 = CONCAT(SQL1,' group by t1.F_ID, t1.F_OrgName, t1.F_ProvinceCode, t1.F_CityCode, t1.F_Climate, t1.F_OrgType, t1.F_BuildArea, t1.F_EnergyPerson, t2.F_Year '); SET SQL2 = CONCAT(SQL2,' group by t1.F_ID, t2.F_Year '); #SELECT SQL1;# 输出拼接sql语句,相当于print #SELECT SQL2;# 输出拼接sql语句,相当于print SET SQLTotal='select AA.F_OrgName, AA.F_ProvinceCode,AA.F_Climate, AA.F_OrgType, AA.F_BuildArea, AA.F_EnergyPerson, AA.F_Year, AA.ZBM+BB.ZBM AS F_TotalZBM, CASE WHEN AA.F_BuildArea=0 THEN 0 ELSE (AA.ZBM+BB.ZBM )/AA.F_BuildArea END AS F_PerAreaZBM, CASE WHEN AA.F_EnergyPerson=0 THEN 0 ELSE (AA.ZBM+BB.ZBM )/AA.F_EnergyPerson END AS F_PerPersonZBM, AA.TPF_HL+BB.TPF_DL+BB.TPF_RL AS F_TotalTPF,AA.TPF_HL,BB.TPF_DL,BB.TPF_RL, CASE WHEN AA.F_BuildArea=0 THEN 0 ELSE (AA.TPF_HL+BB.TPF_DL+BB.TPF_RL )/AA.F_BuildArea END AS F_PerAreaTPF, CASE WHEN AA.F_EnergyPerson=0 THEN 0 ELSE (AA.TPF_HL+BB.TPF_DL+BB.TPF_RL )/AA.F_EnergyPerson END AS F_PerPersonTPF '; SET SQLTotal = CONCAT(SQLTotal,' from (', SQL1,') AA JOIN (',SQL2,') BB ON AA.F_ID=BB.F_ID AND AA.F_Year=BB.F_Year '); SET SQLTotal = CONCAT(SQLTotal,' ORDER BY AA.F_ID,AA.F_Year '); #SELECT SQLTotal;# 输出拼接sql语句,相当于print SET @SQLTotal = SQLTotal; prepare s1 from @SQLTotal; EXECUTE s1 ; deallocate prepare s1 ; END
BEGIN /* 存储过程名称:Pro_Get_CO2 存储过程功能说明:获得近5年的能耗数据,碳排放数据 创建日期: 涉及的表或试图: 表或试图名称 用途 存储过程调用: 存储过程名称 用途 NONE 参数说明: 传入参数: 参数名 说明 ivF_Year 年度 返回字段说明: 调用示例: CALL Pro_Get_EnergyData(ivF_Year); CALL Pro_Get_EnergyData('2018'); */ declare SQL0 varchar(5000); declare SQL1 varchar(5000); declare SQL2 varchar(5000); declare SQLTotal varchar(5000); declare SQLQuery varchar(5000); -- 构造近5年列表 SET SQL0=CONCAT('SELECT @cdate :=@cdate-1 AS F_Year FROM (SELECT @cdate := ',ivF_Year+1,' FROM at_sys_errorlog LIMIT 5 ) t4'); -- 一次能源碳排放总量 SET SQL1='select t1.F_ID, t1.F_OrgName, t1.F_ProvinceCode, t1.F_CityCode, t1.F_Climate, t1.F_OrgType, t1.F_BuildArea, t1.F_EnergyPerson, t2.F_Year, sum(t2.F_Value*t3.F_NCV*t3.F_EF) as TPF_HL,sum(t2.F_Value*t3.F_Formular) as ZBM FROM t_organization t1 join t_energy_data t2 ON t1.F_ID = t2.F_OrgID join v_zjpflist t3 on t2.F_FieldID=t3.F_FieldID where t3.F_IsSum=1 '; SET SQL1 = CONCAT(SQL1,' and t2.F_Year>= ', ivF_Year-4,' and t2.F_Year<= ', ivF_Year); -- 二次能源碳排放总量 SET SQL2='select t1.F_ID, t2.F_Year, sum(CASE WHEN t3.F_CalcType = 2 THEN(t2.F_Value * t3.F_EF)END ) as TPF_DL, sum(CASE WHEN t3.F_CalcType = 3 THEN(t2.F_Value * t3.F_EF) END ) as TPF_RL, sum(t2.F_Value*t3.F_Formular) as ZBM FROM t_organization t1 join t_energy_data t2 ON t1.F_ID = t2.F_OrgID join v_jjpflist t3 on t2.F_FieldID=t3.F_FieldID AND t1.F_ID =T3.F_OrgID where t3.F_IsSum=1 '; SET SQL2 = CONCAT(SQL2,' and t2.F_Year>= ', ivF_Year-4,' and t2.F_Year<= ', ivF_Year); SET SQL1 = CONCAT(SQL1,' group by t1.F_ID, t1.F_OrgName, t1.F_ProvinceCode, t1.F_CityCode, t1.F_Climate, t1.F_OrgType, t1.F_BuildArea, t1.F_EnergyPerson, t2.F_Year '); SET SQL2 = CONCAT(SQL2,' group by t1.F_ID, t2.F_Year '); #SELECT SQL1;# 输出拼接sql语句,相当于print #SELECT SQL2;# 输出拼接sql语句,相当于print SET SQLTotal='select AA.F_Year, SUM(AA.F_BuildArea) AS F_BuildArea, SUM(AA.F_EnergyPerson) AS F_EnergyPerson, SUM(AA.ZBM + BB.ZBM) AS F_TotalZBM, SUM(AA.TPF_HL + BB.TPF_DL + BB.TPF_RL) AS F_TotalTPF, SUM(AA.TPF_HL) AS TPF_HL, SUM(BB.TPF_DL) AS TPF_DL, SUM(BB.TPF_RL) AS TPF_RL '; SET SQLTotal = CONCAT(SQLTotal,' from (', SQL1,') AA JOIN (',SQL2,') BB ON AA.F_ID=BB.F_ID AND AA.F_Year=BB.F_Year '); SET SQLTotal = CONCAT(SQLTotal,' GROUP BY AA.F_Year '); #SELECT SQLTotal; SET SQLQuery ='select BBB.F_Year, AAA.F_BuildArea, AAA.F_EnergyPerson,AAA.F_TotalZBM, CASE WHEN AAA.F_BuildArea=0 THEN 0 ELSE AAA.F_TotalZBM/AAA.F_BuildArea END AS F_PerAreaZBM, CASE WHEN AAA.F_EnergyPerson=0 THEN 0 ELSE AAA.F_TotalZBM/AAA.F_EnergyPerson END AS F_PerPersonZBM, AAA.F_TotalTPF, CASE WHEN AAA.F_BuildArea=0 THEN 0 ELSE AAA.F_TotalTPF/AAA.F_BuildArea END AS F_PerAreaTPF, CASE WHEN AAA.F_EnergyPerson=0 THEN 0 ELSE AAA.F_TotalTPF/AAA.F_EnergyPerson END AS F_PerPersonTPF '; SET SQLQuery = CONCAT(SQLQuery,' from (', SQL0,') BBB LEFT JOIN (',SQLTotal,') AAA ON AAA.F_Year=BBB.F_Year '); SET SQLQuery = CONCAT(SQLQuery,' ORDER BY BBB.F_Year '); #SELECT SQLQuery; SET @SQLQuery = SQLQuery; prepare s1 from @SQLQuery; EXECUTE s1 ; deallocate prepare s1 ; END
存储过程调用方式:
CALL Pro_Get_CO2('2018','','','');
CALL Pro_Get_EnergyData('2017');
CALL Pro_Get_Carbon_OrgType('2014');
CALL Pro_Get_Carbon_Climate('2014');
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网
猜你喜欢:- MySQL 建表语句转 PostgreSQL 建表语句全纪录
- Go语言开发-过程式编程-通信和并发语句-Select语句
- SQL语句优化之JOIN和LEFT JOIN 和 RIGHT JOIN语句的优化
- Python 条件语句
- Python 循环语句
- Python break 语句
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
Hive编程指南
卡普廖洛 (Edward Capriolo)、万普勒 (Dean Wampler)、卢森格林 (Jason Rutherglen) / 曹坤 / 人民邮电出版社 / 2013-12-1 / 69
市场中第一本Hive图书。 Hive在Hadoop系统中的应用趋势比较可观。一起来看看 《Hive编程指南》 这本书的介绍吧!