mysql 存储过程实现根据开始结束时间动态补点，关联查询
1.场景声明：在用报表工具开发报表时，由于数据查询结果依赖数据库中现有的数据，当筛选条件在数据库中无命中记录时查询结果往往是空的，这本符合条件，但有时用户又希望看到筛选条件全覆盖的查询结果，为此，sql语句必须根据查询结果动态伸缩，以达到预期的效果
2.实现思路：由于这种复杂的数据结果往往无法将查询的sql语句写死，需要动态构建sql语句，所以考虑用存储过程实现，存储过程只传入参的筛选条件，存储过程根据一定的规则while循环将需要的点补全后作为表A，将条件查询结果集作为表B，通过B right join A实现补点效果
3.表B查询sql及查询结果：
SELECT REPLACE ( h.日期, ":00", "h" ) AS 日期, round( REPLACE ( format( h.上限值, 2 ), ',', '' ), 2 ) AS 上限值 FROM ( SELECT concat( substr( g.日期, 6, 6 ), "(", g.时刻, ")" ) AS 日期, ( g.统调负荷 + g.东送负荷 - ( g.火电检修容量 - g.自备检修容量 ) * 0.5 * 0.9 - g.自备检修容量 * 0.85 * 0.9 ) AS 上限值 FROM ( SELECT e.日期, e.时刻, e.统调负荷, e.东送负荷, f.自备检修容量, f.火电检修容量 FROM ( SELECT a.日期, a.时刻, a.预测值 AS 统调负荷, b.预测值 AS 东送负荷 FROM ( SELECT ( @ROW := @ROW + 1 ) AS 序号, businessTime AS 时刻, businessDate AS 日期, planValue AS 预测值 FROM ( SELECT @ROW := 0 ) t, next_nified_dispatch_load_forecast WHERE businessDate >= '2022-06-07' AND businessDate <= '2022-06-17' GROUP BY businessDate, businessTime ORDER BY ( @ROW := @ROW + 1 ) ) a INNER JOIN ( SELECT ( @ROW := @ROW + 1 ) AS 序号, businessTime AS 时刻, businessDate AS 日期, planValue AS 预测值 FROM ( SELECT @ROW := 0 ) t, next_eastward_ran_plan WHERE businessDate >= '2022-06-07' AND businessDate <= '2022-06-17' GROUP BY businessDate, businessTime ORDER BY ( @ROW := @ROW + 1 ) ) b ON a.日期 = b.日期 AND a.时刻 = b.时刻 ORDER BY a.日期, a.时刻 ) e LEFT JOIN ( SELECT c.日期, c.自备检修容量, d.火电检修容量 FROM ( SELECT ( @ROW := @ROW + 1 ) AS 序号, ( SELECT SUM( captive_capacity ) 自备总容量 FROM spot_base_data.t_node_capacity_common WHERE type = 1 ) - CAST( ROUND( SUM( capacity ), 3 ) AS DECIMAL ) 自备检修容量, businessDate AS 日期 FROM ( SELECT @ROW := 0 ) t, maintenance_information_import WHERE businessDate >= '2022-06-07' AND businessDate <= '2022-06-17' AND equipment = '机组' AND equipmentDispatchingName REGEXP 'xx|yy' GROUP BY businessDate ORDER BY ( @ROW := @ROW + 1 ) ) c INNER JOIN ( SELECT ( @ROW := @ROW + 1 ) AS 序号, ( SELECT SUM( power_installation_capacity ) + SUM( captive_capacity ) 装机总容量 FROM spot_base_data.t_node_capacity_common WHERE type = 1 ) - CAST( ROUND( SUM( capacity ), 3 ) AS DECIMAL ) 火电检修容量, businessDate AS 日期 FROM ( SELECT @ROW := 0 ) t, maintenance_information_import WHERE businessDate >= '2022-06-07' AND businessDate <= '2022-06-17' AND equipment = '机组' GROUP BY businessDate ORDER BY ( @ROW := @ROW + 1 ) ) d ON c.日期 = d.日期 ) f ON f.日期 = e.日期 ) g ) h WHERE 日期 REGEXP '04:00|06:00|08:00|10:00|12:00|14:00|16:00|18:00|20:00|22:00|11:00|17:00|05:00'
查询结果：
日期 上限值
06-07(04h) 12776.67
06-07(05h) 12782.67
06-07(06h) 13387.67
06-07(08h) 14223.27
06-07(10h) 15172.57
06-07(11h) 15562.17
06-07(12h) 14907.97
06-07(14h) 14273.77
06-07(16h) 14591.17
06-07(17h) 15226.17
06-07(18h) 15200.47
06-07(20h) 15762.37
06-07(22h) 15800.77
06-08(04h)
06-08(05h)
06-08(06h)
06-08(08h)
06-08(10h)
06-08(11h)
06-08(12h)
06-08(14h)
06-08(16h)
06-08(17h)
06-08(18h)
06-08(20h)
06-08(22h)
06-09(04h) 13330.37
06-09(05h) 13403.77
06-09(06h) 13882.47
06-09(08h) 14784.47
06-09(10h) 15672.77
06-09(11h) 16096.77
06-09(12h) 15407.07
06-09(14h) 14846.77
06-09(16h) 15237.47
06-09(17h) 15840.17
06-09(18h) 15751.07
06-09(20h) 16016.47
06-09(22h) 15933.47
06-10(04h)
06-10(05h)
06-10(06h)
06-10(08h)
06-10(10h)
06-10(11h)
06-10(12h)
06-10(14h)
06-10(16h)
06-10(17h)
06-10(18h)
06-10(20h)
06-10(22h)
06-11(04h)
06-11(05h)
06-11(06h)
06-11(08h)
06-11(10h)
06-11(11h)
06-11(12h)
06-11(14h)
06-11(16h)
06-11(17h)
06-11(18h)
06-11(20h)
06-11(22h)
结果可见查询缺少06-12日到06-17日的数据
4.A需借助存储过程动态构建
drop procedure getrange; CREATE PROCEDURE getrange ( IN began date, IN eds date) BEGIN declare sqlx text; set @sqlx = ''; WHILE began <= eds DO SET @sqlx = concat( @sqlx,'select "', RIGHT ( began, 5 ), '(04h)', '" as date union ' ); SET @sqlx = concat( @sqlx,'select "', RIGHT ( began, 5 ), '(05h)', '" as date union ' ); SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(06h)', '" as date union ' ); SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(08h)', '" as date union ' ); SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(10h)', '" as date union ' ); SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(11h)', '" as date union ' ); SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(12h)', '" as date union ' ); SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(14h)', '" as date union ' ); SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(16h)', '" as date union ' ); SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(17h)', '" as date union ' ); SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(18h)', '" as date union ' ); SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(20h)', '" as date union ' ); if began = eds then SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(22h)" as date'); else SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(22h)', '" as date union ' ); end if; SET began = adddate( began, 1 ); END WHILE; PREPARE stmt FROM @sqlx; EXECUTE stmt; END;
调用和结果：
CALL getrange ('2022-06-07','2022-06-17' );
date
06-07(04h)
06-07(05h)
06-07(06h)
06-07(08h)
06-07(10h)
06-07(11h)
06-07(12h)
06-07(14h)
06-07(16h)
06-07(17h)
06-07(18h)
06-07(20h)
06-07(22h)
06-08(04h)
06-08(05h)
06-08(06h)
06-08(08h)
06-08(10h)
06-08(11h)
06-08(12h)
06-08(14h)
06-08(16h)
06-08(17h)
06-08(18h)
06-08(20h)
06-08(22h)
06-09(04h)
06-09(05h)
06-09(06h)
06-09(08h)
06-09(10h)
06-09(11h)
06-09(12h)
06-09(14h)
06-09(16h)
06-09(17h)
06-09(18h)
06-09(20h)
06-09(22h)
06-10(04h)
06-10(05h)
06-10(06h)
06-10(08h)
06-10(10h)
06-10(11h)
06-10(12h)
06-10(14h)
06-10(16h)
06-10(17h)
06-10(18h)
06-10(20h)
06-10(22h)
06-11(04h)
06-11(05h)
06-11(06h)
06-11(08h)
06-11(10h)
06-11(11h)
06-11(12h)
06-11(14h)
06-11(16h)
06-11(17h)
06-11(18h)
06-11(20h)
06-11(22h)
06-12(04h)
06-12(05h)
06-12(06h)
06-12(08h)
06-12(10h)
06-12(11h)
06-12(12h)
06-12(14h)
06-12(16h)
06-12(17h)
06-12(18h)
06-12(20h)
06-12(22h)
06-13(04h)
06-13(05h)
06-13(06h)
06-13(08h)
06-13(10h)
06-13(11h)
06-13(12h)
06-13(14h)
06-13(16h)
06-13(17h)
06-13(18h)
06-13(20h)
06-13(22h)
06-14(04h)
06-14(05h)
06-14(06h)
06-14(08h)
06-14(10h)
06-14(11h)
06-14(12h)
06-14(14h)
06-14(16h)
06-14(17h)
06-14(18h)
06-14(20h)
06-14(22h)
06-15(04h)
06-15(05h)
06-15(06h)
06-15(08h)
06-15(10h)
06-15(11h)
06-15(12h)
06-15(14h)
06-15(16h)
06-15(17h)
06-15(18h)
06-15(20h)
06-15(22h)
06-16(04h)
06-16(05h)
06-16(06h)
06-16(08h)
06-16(10h)
06-16(11h)
06-16(12h)
06-16(14h)
06-16(16h)
06-16(17h)
06-16(18h)
06-16(20h)
06-16(22h)
06-17(04h)
06-17(05h)
06-17(06h)
06-17(08h)
06-17(10h)
06-17(11h)
06-17(12h)
06-17(14h)
06-17(16h)
06-17(17h)
06-17(18h)
06-17(20h)
06-17(22h)
如此便可构建出表B
5.接下来对存储过程进行改造，将表A当作字符串放入存储过程，right join表B即可获取到预期结果
DROP PROCEDURE getrange3; CREATE PROCEDURE getrange3 ( IN began date, IN eds date) BEGIN declare leftsql text; declare sqlx text; set @leftsql = ''; set @sqlx = ''; set @leftsql = concat(@leftsql,"select u.date as 日期, i.上限值 from (SELECT REPLACE ( h.日期, ':00', 'h' ) AS 日期, round( REPLACE ( format( h.上限值, 2 ), ',', '' ), 2 ) AS 上限值 FROM ( SELECT concat( substr( g.日期, 6, 6 ), '(', g.时刻, ')' ) AS 日期, ( g.统调负荷 + g.东送负荷 - ( g.火电检修容量 - g.自备检修容量 ) * 0.5 * 0.9 - g.自备检修容量 * 0.85 * 0.9 ) AS 上限值 FROM ( SELECT e.日期, e.时刻, e.统调负荷, e.东送负荷, f.自备检修容量, f.火电检修容量 FROM ( SELECT a.日期, a.时刻, a.预测值 AS 统调负荷, b.预测值 AS 东送负荷 FROM ( SELECT ( @ROW := @ROW + 1 ) AS 序号, businessTime AS 时刻, businessDate AS 日期, planValue AS 预测值 FROM ( SELECT @ROW := 0 ) t, next_nified_dispatch_load_forecast WHERE businessDate >= '",began, "' AND businessDate <='", eds , "'GROUP BY businessDate, businessTime ORDER BY ( @ROW := @ROW + 1 ) ) a INNER JOIN ( SELECT ( @ROW := @ROW + 1 ) AS 序号, businessTime AS 时刻, businessDate AS 日期, planValue AS 预测值 FROM ( SELECT @ROW := 0 ) t, next_eastward_ran_plan WHERE businessDate >= '",began, "' AND businessDate <='", eds , "'GROUP BY businessDate, businessTime ORDER BY ( @ROW := @ROW + 1 ) ) b ON a.日期 = b.日期 AND a.时刻 = b.时刻 ORDER BY a.日期, a.时刻 ) e LEFT JOIN ( SELECT c.日期, c.自备检修容量, d.火电检修容量 FROM ( SELECT ( @ROW := @ROW + 1 ) AS 序号, ( SELECT SUM( captive_capacity ) 自备总容量 FROM spot_base_data.t_node_capacity_common WHERE type = 1 ) - CAST( ROUND( SUM( capacity ), 3 ) AS DECIMAL ) 自备检修容量, businessDate AS 日期 FROM ( SELECT @ROW := 0 ) t, maintenance_information_import WHERE businessDate >= '",began, "' AND businessDate <='", eds , "' AND equipment = '机组' AND equipmentDispatchingName REGEXP 'xx|yy' GROUP BY businessDate ORDER BY ( @ROW := @ROW + 1 ) ) c INNER JOIN ( SELECT ( @ROW := @ROW + 1 ) AS 序号, ( SELECT SUM( power_installation_capacity ) + SUM( captive_capacity ) 装机总容量 FROM spot_base_data.t_node_capacity_common WHERE type = 1 ) - CAST( ROUND( SUM( capacity ), 3 ) AS DECIMAL ) 火电检修容量, businessDate AS 日期 FROM ( SELECT @ROW := 0 ) t, maintenance_information_import WHERE businessDate >= '",began, "' AND businessDate <='", eds , "' AND equipment = '机组' GROUP BY businessDate ORDER BY ( @ROW := @ROW + 1 ) ) d ON c.日期 = d.日期 ) f ON f.日期 = e.日期 ) g ) h WHERE 日期 REGEXP '04:00|06:00|08:00|10:00|12:00|14:00|16:00|18:00|20:00|22:00|11:00|17:00|05:00') i"); WHILE began <= eds DO SET @sqlx = concat( @sqlx,'select "', RIGHT ( began, 5 ), '(04h)', '" as date union ' ); SET @sqlx = concat( @sqlx,'select "', RIGHT ( began, 5 ), '(05h)', '" as date union ' ); SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(06h)', '" as date union ' ); SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(08h)', '" as date union ' ); SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(10h)', '" as date union ' ); SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(11h)', '" as date union ' ); SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(12h)', '" as date union ' ); SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(14h)', '" as date union ' ); SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(16h)', '" as date union ' ); SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(17h)', '" as date union ' ); SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(18h)', '" as date union ' ); SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(20h)', '" as date union ' ); if began = eds then SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(22h)" as date'); else SET @sqlx = concat( @sqlx, 'select "', RIGHT ( began, 5 ), '(22h)', '" as date union ' ); end if; SET began = adddate( began, 1 ); END WHILE; set @leftsql = concat(@leftsql,' right join (',@sqlx,') u on i.日期=u.date'); PREPARE stmt FROM @leftsql; EXECUTE stmt; END;
调用和查询结果：
CALL getrange3 ('2022-06-07','2022-06-17' );
结果：
日期 上限值
06-07(04h) 12776.67
06-07(05h) 12782.67
06-07(06h) 13387.67
06-07(08h) 14223.27
06-07(10h) 15172.57
06-07(11h) 15562.17
06-07(12h) 14907.97
06-07(14h) 14273.77
06-07(16h) 14591.17
06-07(17h) 15226.17
06-07(18h) 15200.47
06-07(20h) 15762.37
06-07(22h) 15800.77
06-08(04h)
06-08(05h)
06-08(06h)
06-08(08h)
06-08(10h)
06-08(11h)
06-08(12h)
06-08(14h)
06-08(16h)
06-08(17h)
06-08(18h)
06-08(20h)
06-08(22h)
06-09(04h) 13330.37
06-09(05h) 13403.77
06-09(06h) 13882.47
06-09(08h) 14784.47
06-09(10h) 15672.77
06-09(11h) 16096.77
06-09(12h) 15407.07
06-09(14h) 14846.77
06-09(16h) 15237.47
06-09(17h) 15840.17
06-09(18h) 15751.07
06-09(20h) 16016.47
06-09(22h) 15933.47
06-10(04h)
06-10(05h)
06-10(06h)
06-10(08h)
06-10(10h)
06-10(11h)
06-10(12h)
06-10(14h)
06-10(16h)
06-10(17h)
06-10(18h)
06-10(20h)
06-10(22h)
06-11(04h)
06-11(05h)
06-11(06h)
06-11(08h)
06-11(10h)
06-11(11h)
06-11(12h)
06-11(14h)
06-11(16h)
06-11(17h)
06-11(18h)
06-11(20h)
06-11(22h)
06-12(04h)
06-12(05h)
06-12(06h)
06-12(08h)
06-12(10h)
06-12(11h)
06-12(12h)
06-12(14h)
06-12(16h)
06-12(17h)
06-12(18h)
06-12(20h)
06-12(22h)
06-13(04h)
06-13(05h)
06-13(06h)
06-13(08h)
06-13(10h)
06-13(11h)
06-13(12h)
06-13(14h)
06-13(16h)
06-13(17h)
06-13(18h)
06-13(20h)
06-13(22h)
06-14(04h)
06-14(05h)
06-14(06h)
06-14(08h)
06-14(10h)
06-14(11h)
06-14(12h)
06-14(14h)
06-14(16h)
06-14(17h)
06-14(18h)
06-14(20h)
06-14(22h)
06-15(04h)
06-15(05h)
06-15(06h)
06-15(08h)
06-15(10h)
06-15(11h)
06-15(12h)
06-15(14h)
06-15(16h)
06-15(17h)
06-15(18h)
06-15(20h)
06-15(22h)
06-16(04h)
06-16(05h)
06-16(06h)
06-16(08h)
06-16(10h)
06-16(11h)
06-16(12h)
06-16(14h)
06-16(16h)
06-16(17h)
06-16(18h)
06-16(20h)
06-16(22h)
06-17(04h)
06-17(05h)
06-17(06h)
06-17(08h)
06-17(10h)
06-17(11h)
06-17(12h)
06-17(14h)
06-17(16h)
06-17(17h)
06-17(18h)
06-17(20h)
06-17(22h)
ok，我们完成了动态补点，完美收工！！！
