mysql - 使用准备好的语句的复杂 mysql 查询问题
问题描述
不执行以下过程。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
解决方案
- 为了在准备好的语句中包含变量,我使用了字符串连接。您的查询将给出错误,因为
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
推荐阅读
- go - 如何使用 go-guardian JWT 策略主动注销用户?
- wordpress - WooCommerce:创建产品页面而不是使用产品过滤器
- amazon-web-services - CMD 上是否有命令通过无服务器框架列出 AWS 上的所有部署 lambdas(Functions)?
- c - 在 C 中比较两个字符串的问题
- node.js - NodeJS 中的异步 foreach 或 for 循环
- javascript - 我的应用程序可以更改用户的浏览器设置吗?
- datagrip - Datagrip - 无法启动
- ios - Swift - 放置正确的导航箭头方向
- javascript - onbeforeunload 事件确认
- javascript - 如何用更多跨度标签包装html跨度标签数组?