<h1>表说明</h1>
<ul>
<li>表名：固定的前缀+%Y%m%d, 如 <code>summary_20220809</code></li>
<li>表结构：count BIGINT, id BIGINT</li>
</ul>
<h1>查询单个表值示例</h1>
<pre><code class="language-mysql">select count from @table where id = @id;
</code></pre>
<h1>思路</h1>
<p>是否只能用循环日期+动态 sql 处理？</p>
<p>伪代码如下：</p>
<pre><code class="language-sql">create PROCEDURE findMaxDay(id INT, mouth VARCHAR(255);)
begin
-- 省略一些赋值
WHILE x <= 31 DO
set table_name = CONCAT('summary_',mouth, lpad(x,2,0));
-- 查询单表结果并赋值
set sql = CONCAT("select count INTO @tmp from '", table_name, "' where id = '", id,"'");
PREPARE stmt FROM @sql;
EXECUTE stmt;
deallocate prepare stmt;
-- 比较和更新结果
set @day = IF( @tmp > @max, lpad(x,2,0), @day);
set @max = max(tmp, max);
set x = x + 1;
END WHILE;
-- 输出结果
select day;
end
</code></pre>
<p>请大佬看看这样的思路对不对？有没有更好的方案。</p>
渲染出下面 sql ，然后直接执行就好了呗。 如果你过滤条件都走的索引的话，理论上应该还行
select * from
(select 'summary_20220809' , max(id) `max_id` from summary_20220809
union all
select 'summary_20220808' , max(id) `max_id` from summary_20220808
union all
select 'summary_20220807' , max(id) `max_id` from summary_20220807
union all
...
) a
order by `max_id` desc limit 1
