mysql - 使用预准备语句的复杂 mysql 查询的问题

Question

以下过程不执行。 int_id是用户定义的输入

我的目标是使用STR_TABLE_NAME作为表的动态变量。注意:但是，如果我将其替换为目标表的名称并删除“SET @prep_stmt ="。

BEGIN

DECLARE STR_TABLE_NAME VARCHAR(100) DEFAULT NULL;
SELECT `table_source` INTO STR_TABLE_NAME FROM `list_repository` WHERE 
id=`int_id` LIMIT 1;

DROP TABLE IF EXISTS `loyaltytry`;
SET @prep_stmt = CREATE TABLE `loyaltytry` as (SELECT Months as month, 
Number_of_New_Customers as `new_customers` , `Number_of_Repeat_Customers`  
as `repeat_customers`
from
(SELECT  monthname(Months) as Months, month(Months)  as `Month_number`,
sum(CASE WHEN REP_COUNT ='no' then cnts end) as `Number_of_New_Customers`,
sum(CASE WHEN REP_COUNT = 'yes' then cnts end) as 
`Number_of_Repeat_Customers`
from (
SELECT months,REP_COUNT,count(*) as cnts
from (
    SELECT (date_commande_client) as Months , numero,
    CASE WHEN cnt > 1 THEN 'yes'
    ELSE 'no'
    END AS REP_COUNT
    from ( 
    SELECT COUNT(*) as cnt, date_commande_client, numero
    FROM  STR_TABLE_NAME           
        WHERE YEAR(date_commande_client)=2017
        AND intitule IN (SELECT showroom_name FROM `showrooms` WHERE 
id_region=`int_id`)
       group by date_commande_client , numero) as tmp) as final
    GROUP BY Months,REP_COUNT ) as tmp1
GROUP BY monthname(Months),month(Months)
ORDER BY Month_number) as finalll
);

PREPARE stmt FROM @prep_stmt; 
EXECUTE stmt; 
DEALLOCATE PREPARE stmt;

END

Answer 1

• 为了在准备语句中包含变量，我使用了字符串连接。您的查询将给出错误，因为 STR_TABLE_NAME 将被视为表名称并且找不到任何内容。
• 从输入参数中删除 (`) 反引号。

BEGIN

    DECLARE STR_TABLE_NAME VARCHAR(100) DEFAULT NULL;
    SELECT table_source INTO STR_TABLE_NAME FROM list_repository WHERE id = int_id LIMIT 1;

    DROP TABLE IF EXISTS `loyaltytry`;

    SET @prep_stmt = CONCAT('CREATE TABLE `loyaltytry` AS (
                                        SELECT 
                                            Months AS MONTH, 
                                            Number_of_New_Customers AS `new_customers`, 
                                            `Number_of_Repeat_Customers` AS `repeat_customers`
                                        FROM (
                                            SELECT 
                                                MONTHNAME(Months) AS Months, 
                                                MONTH(Months) AS `Month_number`, 
                                                SUM(CASE WHEN REP_COUNT = "no" THEN cnts END) AS `Number_of_New_Customers`, 
                                                SUM(CASE WHEN REP_COUNT = "yes" THEN cnts END) AS `Number_of_Repeat_Customers`
                                            FROM (
                                                SELECT 
                                                    months,
                                                    REP_COUNT, 
                                                    COUNT(*) AS cnts
                                                FROM (
                                                    SELECT 
                                                        (date_commande_client) AS Months, 
                                                        numero, 
                                                        CASE WHEN cnt > 1 THEN "yes" ELSE "no" END AS REP_COUNT
                                                    FROM (
                                                        SELECT 
                                                            COUNT(*) AS cnt, 
                                                            date_commande_client, 
                                                            numero
                                                        FROM ',  STR_TABLE_NAME , 
                                                        ' WHERE YEAR(date_commande_client) = 2017 
                                                            AND intitule IN (
                                                                SELECT 
                                                                    showroom_name
                                                                FROM `showrooms`
                                                                WHERE id_region= ', int_id , ' 
                                                            )
                                                        GROUP BY date_commande_client, numero
                                                    ) AS tmp
                                                ) AS final
                                                GROUP BY Months, REP_COUNT
                                            ) AS tmp1
                                            GROUP BY MONTHNAME(Months), MONTH(Months)
                                            ORDER BY Month_number) AS finalll
                                        )');

    PREPARE stmt FROM @prep_stmt; 
    EXECUTE stmt; 
    DEALLOCATE PREPARE stmt;

END