mariadb 如何将第一个选择查询的结果在第二个查询中用作存储过程中的列？

z4iuyo4d 于 2022-11-08 发布在其他

关注(0)|答案(1)|浏览(70)

我在存储过程中遇到问题。
在我的存储过程代码中，第一个查询返回的列将在第二个查询中用作选择列，但它没有提供列的值，而是在结果中显示列。请告诉我，我做错了什么

CREATE DEFINER=`root`@`localhost` PROCEDURE `database`.`getProcedure`(
        id int(11),
        reportType varchar(20),
        tableName varchar(30),
        startDate varchar(30),
        endDate varchar(30)
    )
BEGIN

    SET @lead = (SELECT CONCAT('SUM(',GROUP_CONCAT('`',CONCAT(tableName,'`.`',`table2`.`database_field`) SEPARATOR "`+"),'`)')
    FROM table1
    LEFT JOIN table2
    ON table2.id = table1.attribute_id 
    WHERE table1.id = id;

    SELECT 
        COUNT(DISTINCT(table1.c1)) AS `c1`, 
        sum(table1.c2) AS `c2`,
        sum(table1.c3) AS `c3`, 
        sum(table1.c4) AS `c4`,
        @lead AS `c5`
   FROM table3 
   LEFT JOIN table4 ON table3.id = table4.table3_id
   WHERE (table3.id = id 
   AND table3.date >= startDate 
   AND table3.date <= endDate);
END

在c5中显示的结果为SUM（table3 . c6 + table3 . c7 + table3 . c8）
但结果应该是SUM（table3 . c6 + table3 . c7 + table3 . c8）的值

mariadb

来源：https://stackoverflow.com/questions/73121825/how-to-use-first-select-querys-result-in-second-query-as-column-in-stored-proce

1条答案

按热度按时间

p5cysglq1#

正如Hartmut Holzgraefe所说，解决方案是

CREATE DEFINER=`root`@`localhost` PROCEDURE `database`.`getProcedure`(
    id int(11),
    reportType varchar(20),
    tableName varchar(30),
    startDate varchar(30),
    endDate varchar(30)
)
BEGIN
SET @sql = NULL;
SELECT CONCAT('SUM(',GROUP_CONCAT('`',CONCAT(tableName,'`.`',`table2`.`database_field`) SEPARATOR "`+"),'`)') as @sql
FROM table1
LEFT JOIN table2
ON table2.id = table1.attribute_id 
WHERE table1.id = id;

SET @sql = CONCAT('SELECT 
    COUNT(DISTINCT(table1.c1)) AS `c1`, 
    sum(table1.c2) AS `c2`,
    sum(table1.c3) AS `c3`, 
    sum(table1.c4) AS `c4`,',
    @sql,' AS `c5`
FROM table3 
LEFT JOIN table4 ON table3.id = table4.table3_id
WHERE (table3.id = ',id,' 
AND table3.date >= "',startDate,'" 
AND table3.date <= "',endDate,'");');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END

赞(0）回复(0）举报 2022-11-08

我来回答

mariadb 如何将第一个选择查询的结果在第二个查询中用作存储过程中的列？

1条答案

相关问题

热门标签

最新问答