MySQL 存储过程拼接两个变量值 mysql存储过程拼接动态查询sql

转载

mob64ca13f587aa 2024-04-22 00:38:03

文章标签 MySQL 存储过程拼接两个变量值 sql ci Time 文章分类 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，我们完成了动态补点，完美收工！！！

TRANSLATE with x

English

Arabic	Hebrew	Polish
Bulgarian	Hindi	Portuguese
Catalan	Hmong Daw	Romanian
Chinese Simplified	Hungarian	Russian
Chinese Traditional	Indonesian	Slovak
Czech	Italian	Slovenian
Danish	Japanese	Spanish
Dutch	Klingon	Swedish
English	Korean	Thai
Estonian	Latvian	Turkish
Finnish	Lithuanian	Ukrainian
French	Malay	Urdu
German	Maltese	Vietnamese
Greek	Norwegian	Welsh
Haitian Creole	Persian