我无法将查询移动到存储过程中。我需要将逗号分隔的字符串传递给参数yr_model_no
两个人IN
条款。我已经阅读了一些线程并决定使用准备语句。原始查询可以很好地获取多行,但是在存储过程中,当我用call load_things('1283943kd9,2e9kk389334','53')
(第一个是型号列表,第二个是用户 ID),它只能从逗号分隔字符串的第一个值中获取一行。一开始以为是长度问题yr_model_no
参数,但将其更改为varchar(200)
会给我未知的专栏2e9kk389334
在“where 子句”错误中。有人可以帮我找出以下代码中的准备语句或参数有什么问题吗?
这是一个类似的小提琴例子 http://sqlfiddle.com/#!2/9b7d60/5我的表架构和查询。
DELIMITER ;;
CREATE PROCEDURE `load_things` (IN `yr_model_no` varchar(20), IN `yr_app_id` int(5))
BEGIN
SET @s =
CONCAT('
SELECT * FROM
(
SELECT COUNT( c.app_id ) AS users_no, ROUND( AVG( c.min ) , 1 ) AS avg_min, ROUND( AVG( c.max ) , 1 ) AS avg_max, a.mid, a.likes, a.dislikes, b.model_no
FROM `like` a
RIGHT JOIN `model` b ON a.mid = b.mid
LEFT JOIN `details` c ON c.mid = b.mid
WHERE b.model_no IN (',yr_model_no,')
GROUP BY b.model_no
)TAB1
JOIN
(
SELECT a.app_id,b.model_no,IFNULL(c.isbooked,0) AS isbooked,d.min,d.max,e.like_type
FROM `users` a
JOIN `model` b
ON b.model_no IN (',yr_model_no,')
LEFT JOIN `favorite` c
ON c.app_id = a.id
AND c.mid = b.mid
LEFT JOIN `details` d
ON d.app_id = a.id
AND d.mid = b.mid
LEFT JOIN `users_likes` e
ON e.app_id = a.id
AND e.mid = b.mid
WHERE a.id = ',yr_app_id,'
)TAB2
ON TAB1.model_no = TAB2.model_no');
PREPARE stmt from @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt3;
END;;
DELIMITER ;