mysql - mysql sum(column) 从动态表中仅获取一行的值

Question

我有一个有两行的动态表，当我在存储过程中调用 sum(cr) 函数以将总和分配给一个变量时，该函数只返回一行的值，这是我存储过程中的代码：

DROP PROCEDURE IF EXISTS create_table;
CREATE PROCEDURE create_view(IN `query` TEXT, IN `lock_name` CHAR(64), IN 
`timeout` TINYINT UNSIGNED)
BEGIN

-- output from GET_LOCK()
    DECLARE lock_res TINYINT UNSIGNED;

    -- error in query
    DECLARE CONTINUE HANDLER
        FOR SQLEXCEPTION
        SIGNAL SQLSTATE VALUE '45000' SET
                MESSAGE_TEXT  = '[dyn_lock] Dynamic SQL returned an error';

    -- get lock or exit with err
    SET lock_res = GET_LOCK(`lock_name`, IFNULL(`timeout`, 5));
    IF (lock_res IS NULL) THEN
        SIGNAL SQLSTATE VALUE '45000' SET
                MESSAGE_TEXT  = '[dyn_lock] Could not acquire lock: Unknown error';
    ELSEIF (lock_res = 0) THEN
        SIGNAL SQLSTATE VALUE '45000' SET
                MESSAGE_TEXT  = '[dyn_lock] Could not acquire lock: Timeout expired';
    END IF;

    -- create dynamic view
    SET @dyn_sql = CONCAT('CREATE temporary table `', lock_name, '` AS ', query, ';');
    PREPARE stmt_dyn_view FROM @dyn_sql;
    EXECUTE stmt_dyn_view;
    DEALLOCATE PREPARE stmt_dyn_view;

END$$

DROP PROCEDURE IF EXISTS post_entries;
CREATE PROCEDURE `post_entries`(IN `query` TEXT, IN `timeout` TINYINT UNSIGNED, 
posting_type varchar(255))
BEGIN

    DECLARE eof int default 0;
    declare accountID integer(11) default 0;
    declare balance float;
    declare total_amount float;

    CALL create_dynamic_table(`query`, 'dyn_table', `timeout`);

    if(posting_type='receivable')then
        set total_amount=0;
        select sum(dr) into total_amount from dyn_view;
    elseif(posting_type='component receivable') then
        set total_amount=0;
        select sum(cr) into total_amount from dyn_view;
    elseif(posting_type="component payable")then
        set total_amount=0;
        select sum(cr) into total_amount from dyn_view;
    end if;
    drop temporary table dyn_view;

END$$

有两个值要相加，180000 和 90000，但我只得到 90000。

标签： mysqlstored-proceduressum