使用准备好的语句进行复杂的mysql查询的问题 [英] Issue with complex mysql query using prepared statement
本文介绍了使用准备好的语句进行复杂的mysql查询的问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
以下步骤不执行. int_id
是用户定义的输入
The following procedure does not execute. int_id
is a user-defined input
我的目标是使用STR_TABLE_NAME
作为表的动态变量.
NB:但是,查询有效,如果我将其替换为目标表的名称并删除"SET @prep_stmt =.
My goal is to use STR_TABLE_NAME
as a dynamic variable for a table.
NB: the query works however, if i replace it by the name of my target table and remove "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
推荐答案
- 要在Prepared Statement中包含变量,我使用了字符串连接.您的查询将出现错误,因为
STR_TABLE_NAME
将被视为表名,并且找不到任何表名. - 从输入参数中删除(`)反引号.
- To include variables in Prepared Statement, i used string concatenation. Your query will give error because
STR_TABLE_NAME
will be treated as table name and won't find any. - Remove (`) backtick from input parameter.
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
这篇关于使用准备好的语句进行复杂的mysql查询的问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文