Select 语句中的 MySQL 用户定义变量作为表名

Question

我可以使用在先前的 select 语句中定义的用户定义变量作为另一个 select 语句中的表列吗？

例子:

SELECT `name`
INTO @_ptType
FROM `point_types`
WHERE `id` = _case;

SELECT SUM(@_ptType)
INTO _awardedPts
FROM `subscribers_points`
WHERE `subscriber_id` = _subscriber_id
GROUP BY `subscriber_id`;

SELECT `id`
INTO _badgeID
FROM `badges`
WHERE `trigger_point` = _awardedPts
AND `point_type_id` = _case;

假设在第一个 select 中返回到 @_ptType 的 'name' 是 'special'

下一个选择应该返回 sub_id 等匹配的“特殊”列的值，是吗？

我不断收到错误消息:“无数据 - 提取、选择或处理的行为零”

提前致谢。

Answer 1

诀窍是使用动态 SQL 字符串创建准备好的语句:

SET @sql_text = "SELECT SUM(@_ptType)
                 INTO _awardedPts
                 FROM `subscribers_points`
                 WHERE `subscriber_id` = _subscriber_id
                 GROUP BY `subscriber_id`;";

PREPARE stmt FROM @sql_text;

EXECUTE stmt;

DROP PREPARE stmt;