mysql父参数传入子查询_无法使用子查询传递Mysql过程中的参数

我试图创建我认为是一个相当简单的过程调用列名称的参数.

这是我创建的程序：

CREATE DEFINER=`root`@`%` PROCEDURE `test`(in col varchar(225))

BEGIN

-- Column of Averages

DROP temporary TABLE IF EXISTS temp;

CREATE temporary TABLE IF NOT EXISTS temp AS

SELECT col,

Round(Avg(bcount), 0) AS `Average # of Loans`

FROM (SELECT col,

Count(borrower) AS bcount

FROM whatif_dataset

WHERE loan_lienposition = 'first'

GROUP BY col,

Month(Str_to_date(status_fundingdate, '%m/%d/%Y')),

Year(Str_to_date(status_fundingdate, '%m/%d/%Y')))AS dt

GROUP BY col;

END

但是,当我打电话给程序时,我只得到一条记录,即：

Col Average # of Loans

'Passed Parameter' 757

在我看来,参数没有正确传递. MYSQL中有一些限制,不允许我这样做吗？

当我使用特定列名称currentprocessor运行相同的查询时,这是所需的结果：

currentprocessor Average # of Loans

proc1 20

proc2 12

proc3 8

proc4 22

proc5 24

最佳答案 我能够通过使用准备好的声明来纠正这个问题

CREATE DEFINER=`root`@`%` PROCEDURE `test`(in col varchar(225))

BEGIN

-- Column of Averages

DROP temporary TABLE IF EXISTS temp;

SET @sql= CONCAT('SELECT ', col,',',

'Round(Avg(bcount), 0) AS `Average # of Loans`

FROM (SELECT ', col,',

Count(borrower) AS bcount

FROM whatif_dataset

WHERE loan_lienposition = ''first''

GROUP BY ', col,',

Month(Str_to_date(status_fundingdate, "%m/%d/%Y")),

Year(Str_to_date(status_fundingdate, "%m/%d/%Y")))AS dt

GROUP BY ', col);

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END