创建包含动态数据透视表的存储过程时，如何让参数在动态数据透视表中工作？

Question

我已经创建了一个准备好的语句，它使用动态数据透视表为每个用户名创建一列。一切工作正常，但我现在正在尝试将其转换为存储过程。我的问题是，在动态表的‘’中，我有几个参数让我感觉很舒服。

我当前的表和查询。

CREATE TABLE Meeting
(
    ID INT,
    Letter VARCHAR(100),
    Username VARCHAR(100),
    Cost INT,
    billDate VARCHAR(100)
);

INSERT INTO Meeting(ID,Letter,Username,Cost,billDate)
VALUES (1, 'A', 'John' , 10, '2020-11-01');
INSERT INTO Meeting(ID,Letter,Username,Cost,billDate)
VALUES (2, 'B','Jill' , 12, '2020-11-01');
INSERT INTO Meeting(ID,Letter,Username,Cost,billDate)
VALUES (3, 'A','Bob' , 15, '2020-11-21');
INSERT INTO Meeting(ID,Letter,Username,Cost,billDate)
VALUES (4, 'T','John' , 20, '2020-11-11');
INSERT INTO Meeting(ID,Letter,Username,Cost,billDate)
VALUES (5, 'A','John' , 8, '2020-11-01');
INSERT INTO Meeting(ID,Letter,Username,Cost,billDate)
VALUES (6, 'R','Jill' , 11, '2020-11-01');

SET @sql = NULL;
SET @startdate = '2020-11-05';

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when Username = ''',
      Username,
      ''' then Cost end) ',
      Username
    )
  ) INTO @sql
FROM
  Meeting;
SET @sql = CONCAT('SELECT Letter, sum(Cost), ', @sql, ' 
                  FROM Meeting 
                  where billDate >= @startdate
                  GROUP BY Letter');

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

如果我将其转换为存储过程

DELIMITER //

CREATE DEFINER=`root`@`localhost`
PROCEDURE `QueryReport` (startDate VARCHAR(255))
DETERMINISTIC
begin
                                           
SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when Username = ''',
      Username,
      ''' then Cost end) ',
      Username
    )
  ) INTO @sql
FROM
  Meeting;
SET @sql = CONCAT('SELECT Letter, sum(Cost), ', @sql, ' 
                  FROM Meeting 
                  where billDate >= startdate
                  GROUP BY Letter');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
                                           
end //
DELIMITER ;    

我试着调用这个过程

CALL QueryReport ('2020-11-02');

我得到一个错误:未知的列'startDate‘在'where子句’。我认为这可能是来自startDate的‘’弄乱了动态数据透视表的问题。但我不确定。

Answer 1

你的第一段代码是有效的，因为用户定义的变量是全局可用的。定义为参数的变量不是这样的，因此您的concat必须更改

where billDate >= ' , char(39), startdate , char(39) , ' group by ..etc

字符(39)是‘(单引号)的ascii