mysql - 无法在Concat select Prepared语句中传递参数值

我发现存储过程不只是执行这一部分：

          SET @sql
          = CONCAT('SELECT TraineeID, ', @sql, '
                    from tbl_submit_coursefee c
                    where c.BatchID='BID'
                    group by c.TraineeID');

在where子句中，''引号不允许且不带引号查询返回空值，但如果我将参数值直接放在where子句中，则它可以工作。我真的很喜欢这个。
这是我准备好的声明（工作正常）：

SET @sql = NULL;
SELECT
     GROUP_CONCAT(DISTINCT
     CONCAT ('MAX(IF(BillNo = ''', BillNo, ''', CRA, NULL)) AS `Inv.', BillNo, '`')
         )  INTO @sql
    FROM tbl_submit_coursefee
    WHERE BatchID="ID-Welding/FMMTTC-01M/R8/01";

    SET @sql
      = CONCAT('SELECT TraineeID, ', @sql, '
                from tbl_submit_coursefee c
                where c.BatchID="ID-Welding/FMMTTC-01M/R8/01"
                group by c.TraineeID');

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

将此语句放入存储过程（不工作）时：

DELIMITER @@
DROP PROCEDURE GetRetainment @@
CREATE PROCEDURE vtproject.GetRetainment
(IN `BID` VARCHAR(100))
BEGIN
    SET @sql = NULL;
    SELECT
        GROUP_CONCAT(DISTINCT
            CONCAT ('MAX(IF(BillNo = '', BillNo, '', CRA, NULL)) AS `Inv.', BillNo, '`')
        )   INTO @sql
    FROM tbl_submit_coursefee
    WHERE BatchID=BID;

    SET @sql
      = CONCAT('SELECT TraineeID, ', @sql, '
                from tbl_submit_coursefee c
                where c.BatchID='BID'
                group by c.TraineeID');

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

如果Iselect @sql在prepare stmt FROM @sql之前，则输出如下：

SELECT TraineeID, MAX(IF(BillNo = 1, CRA, NULL)) AS `Inv.1`,MAX(IF(BillNo = 2, CRA, NULL)) AS `Inv.2`,MAX(IF(BillNo = 3, CRA, NULL)) AS `Inv.3`,MAX(IF(BillNo = 4, CRA, NULL)) AS `Inv.4`,MAX(IF(BillNo = 5, CRA, NULL)) AS `Inv.5`,MAX(IF(BillNo = 6, CRA, NULL)) AS `Inv.6`,MAX(IF(BillNo = 7, CRA, NULL)) AS `Inv.7` from tbl_submit_coursefee c where c.BatchID=BID group by c.TraineeID

最佳答案

你需要避开这些引语，
这样地：

SET @sql
= CONCAT('SELECT TraineeID, ', @sql, '
from tbl_submit_coursefee c
where c.BatchID=''',BID,'''
group by c.TraineeID');

根据文件：http://dev.mysql.com/doc/refman/5.7/en/string-literals.html

关于mysql - 无法在Concat select Prepared语句中传递参数值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40125013/