如何从动态表名称中选择

一尘不染

如何从动态表名称中选择

mysql

我有这样的桌子

changes201101
changes201102
changes201103
...
changes201201

和whichchanges包含年和月行的表

如何从的更改中选择 whichchanges？

我输入这个查询

SET @b := SELECT CONCAT('changes',year,month) FROM whichchanges;
((((@b should contain now multiple rows of changesYearMonth)))))
SET @x := SELECT * FROM @b;
Prepare stmt FROM @b;
Prepare stmt FROM @x;
Execute stmt;

＃1064-您的SQL语法有误；检查与您的MySQL服务器版本对应的手册以获取正确的语法，以在第1行的’SELECT
CONCAT（’changes’，year，month）FROM changes）’附近使用

阅读 195

2020-05-17

共1个答案

一尘不染

您打开1 (并关闭2 )。删除最后一个：

SELECT CONCAT('changes',year,month) FROM changes

编辑

第二个语句应该是

SET @x := SELECT * FROM (@b) as b;

那行得通，但是不确定那是否是您想要的：

SET @b := 'SELECT CONCAT(''changes'',`year`,`month`) FROM whichchanges';
SET @x := 'SELECT * FROM (SELECT CONCAT(''changes'',`year`,`month`) FROM whichchanges) as b';
Prepare stmt FROM @b;
Prepare stmt FROM @x;
Execute stmt;

编辑2

如果我理解正确，那么您正在寻找一个查询：

select * from changes
where change_column in (select distinct concat(`year`, `month`) from whichchanges)

编辑3

select @b := group_concat(concat(' select * from changes', `year`, `month`, ' union ') separator ' ') as w from whichchanges;
set @b := left(@b, length(@b) - 6);

Prepare stmt FROM @b;
Execute stmt;

SQLFiddle示例

2020-05-17