MySQL 从查询返回的表中选择

Question

我正在尝试从查询返回的 MySQL 表名中进行选择。表名是 table_prefix_date(例如 table_prefix_20160801)。

要获取所有日期和相应的表名，我会这样做:

select concat('db_name.table_prefix_',a.Date)
from (
     select date_format(curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY,'%Y%m%d') as Date
     from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
     cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
     cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where date_format(Date,'%Y-%m-%d') between '2016-08-01' and '2016-08-20'
order by 1 asc;

所有表都有相同的列和索引。只是执行 select * from (queryabove); 失败。

有人可以给我一些工作示例，说明如何从上述查询返回的表中检索数据吗？

Answer 1

需要执行动态SQL语句:

set @sql = (
  select group_concat(replace('select * from @table', '@table', 
                               concat('table_prefix_', date_format(a.Date, '%Y%m%d'))
                             ) separator ' union all ')
  from (select date_sub(curdate(), INTERVAL (a.a + (10 * b.a) + (100 * c.a)) day) as Date
        from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) a cross join
             (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) b cross join
             (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) c
       ) a
  where Date between '2016-08-15' and '2016-08-18'
);

prepare stmt1 from @sql;
execute stmt1; 

Here是上面的 SQL Fiddle。

查询本质上是您使用select 和union all 逻辑的查询。

不过，更重要的是，您不应该设计一组包含相同信息的表格。相反，您应该在插入的大表中有一个日期列。