存储调用 另外一个日期存储

Question

关于 一个存储调用 另外一个 存储（上个月日期）问题
2个存储的代码，分别 call 单独运行是没有问题的，call sp_crosstable() 或者call get_current_date()
代码如下

DROP PROCEDURE IF EXISTS get_current_date;
USE chisheng2019;
DELIMITER &&
CREATE PROCEDURE get_current_date()
BEGIN
SELECT date_format(date_sub(curdate(), interval 1 month),'%Y%m') as date;
END &&
DELIMITER ;
DROP PROCEDURE IF EXISTS sp_crosstable;
SET SESSION GROUP_CONCAT_max_len= 102400;
DELIMITER &&
CREATE PROCEDURE sp_crosstable() reads SQL data
    BEGIN
SET @sql_str = NULL;
SELECT 
GROUP_CONCAT(DISTINCT( 
    CONCAT('SUM(IF(date=\'',date,'\',qty,0)) AS ''',date,'''')) order by subptou1.invono ASC )
INTO @sql_str
from 
subptou1
     inner join subptou2 on subptou2.invono=subptou1.invono
     left join subvendor1 on subvendor1.vendorid=subptou1.vendorid
     where date_format(subptou1.date,'%Y-%m')= date_format(date_sub(curdate(), interval 1 month),'%Y-%m'); 
set @sql_str =
CONCAT('SELECT IFNULL(vendor,\'总计\') AS 客户名称,',
       @sql_str,
       ',sum(IFNULL(subptou2.qty,0)) AS 历史总收数量 
from 
subptou1
     inner join subptou2 on subptou2.invono=subptou1.invono
     left join subvendor1 on subvendor1.vendorid=subptou1.vendorid
         WHERE CONCAT(SUBSTRING(subptou1.date , 1, 4),SUBSTRING(subptou1.date , 6, 2))=202401

     GROUP BY vendor WITH ROLLUP;');
PREPARE stmt from @sql_str;
EXECUTE stmt;
DEALLOCATE prepare stmt;
END &&
DELIMITER ;

/*call sp_crosstable*/
/*call get_current_date*/

我现在遇到的问题是，怎样把例子的 202401替换成 call get_current_date ，或者怎样弄？

Answer 1

引自免费微信小程序：皆我百晓生

你想要在sp_crosstable存储过程中调用get_current_date存储过程以动态获取上个月的日期，并替换其中固定日期202401的部分。要实现这个目标，你需要在构建@sql_str变量时调用get_current_date来获取日期值。

以下是修改后的sp_crosstable存储过程代码：

DROP PROCEDURE IF EXISTS get_current_date;
USE chisheng2019;
DELIMITER &&
CREATE PROCEDURE get_current_date()
BEGIN
SELECT date_format(date_sub(curdate(), interval 1 month),'%Y%m') as date;
END &&
DELIMITER ;

DROP PROCEDURE IF EXISTS sp_crosstable;
SET SESSION GROUP_CONCAT_max_len= 102400;
DELIMITER &&
CREATE PROCEDURE sp_crosstable() reads SQL data
BEGIN
  DECLARE last_month VARCHAR(6);
  
  -- 调用 get_current_date 获取上个月日期并赋值给变量
  CALL get_current_date();
  SELECT @last_month := date INTO @last_month FROM (SELECT @date AS date) AS derived;

  SET @sql_str = NULL;
  SELECT 
    GROUP_CONCAT(DISTINCT(
      CONCAT('SUM(IF(date=\'',date,'\',qty,0)) AS ''',date,'''')
    )) INTO @sql_str
  FROM subptou1
    INNER JOIN subptou2 ON subptou2.invono = subptou1.invono
    LEFT JOIN subvendor1 ON subvendor1.vendorid = subptou1.vendorid
  WHERE date_format(subptou1.date,'%Y-%m') = date_format(@last_month, '%Y-%m');

  SET @sql_str =
    CONCAT('SELECT IFNULL(vendor,\'总计\') AS 客户名称,',
           @sql_str,
           ',sum(IFNULL(subptou2.qty,0)) AS 历史总收数量 
    FROM subptou1
        INNER JOIN subptou2 ON subptou2.invono = subptou1.invono
        LEFT JOIN subvendor1 ON subvendor1.vendorid = subptou1.vendorid
    WHERE CONCAT(SUBSTRING(subptou1.date , 1, 4),SUBSTRING(subptou1.date , 6, 2))=', QUOTE(@last_month),
           ' GROUP BY vendor WITH ROLLUP;');

  PREPARE stmt FROM @sql_str;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END &&
DELIMITER ;

请注意，在MySQL中，直接将存储过程的输出结果赋值给变量可能需要额外处理，因为存储过程的输出不能直接插入到另一个查询的变量中。这里假设date字段能够正确地从get_current_date存储过程中获取并赋值给@last_month变量。实际情况中，你可能需要创建一个临时表或者使用用户定义变量来传递日期值。

然而，MySQL并不支持直接将存储过程的输出作为表达式的一部分。因此，更可行的方法可能是先执行get_current_date()并将结果保存在一个会话变量中，然后在sp_crosstable内部引用该变量。但这种方式在本例中依然存在挑战，因为get_current_date返回的结果是一个行集，而我们需要将其转化为单一的字符串值。

为了解决这个问题，一种可能的做法是将get_current_date改为函数形式，以便可以直接获取所需的格式化日期字符串：

DELIMITER &&
CREATE FUNCTION get_last_month_date() RETURNS VARCHAR(6)
BEGIN
  RETURN date_format(date_sub(curdate(), interval 1 month), '%Y%m');
END &&
DELIMITER ;

-- 然后在 sp_crosstable 中直接使用该函数
-- ...
WHERE CONCAT(SUBSTRING(subptou1.date , 1, 4),SUBSTRING(subptou1.date , 6, 2)) = get_last_month_date();

这样，你就可以在sp_crosstable中的条件语句中直接调用get_last_month_date()函数了。