mysql - 在 Union All 中循环

Question

我正在编写一个存储过程，它将按酒精类别和工作编号显示是/否计数。我开始通过一个联盟对当前的类别—— Wine 、啤酒、威士忌——进行编程，但我想 future 可能会有更多的类别，我想象这个代码的大小越来越大。是否可以在一个循环中执行一个 union all 然后传递 alcohol 类别参数？在互联网上搜索过，但对这个主题知之甚少，因此非常感谢任何帮助或指导。

我的代码的开始...

delimiter $$
create procedure alc_cat_yn (in jid int)
begin
select
cast(concat(jobid,' - Wine') 
as char(50)) as `Job Number - Consumed Yesterday`
,sum(case when wine_id=1 then 1 else 0 end) as y
,sum(case when wine_id=2 then 1 else 0 end) as n
from demos
where jobid=jid
group by jobid
union all
select
cast(concat(jobid,' - Beer') 
as char(50)) as `Job Number - Consumed Yesterday`
,sum(case when beer_id=1 then 1 else 0 end) as y
,sum(case when beer_id=2 then 1 else 0 end) as n
from demos
where jobid=jid
group by jobid
union all
select
cast(concat(jobid,' - Whisky') 
as char(50)) as `Job Number - Consumed Yesterday`
,sum(case when whisky_id=1 then 1 else 0 end) as y
,sum(case when whisky_id=2 then 1 else 0 end) as n
from demos
where jobid=jid
group by jobid;
end

Answer 1

理论上，您可以有一个循环，它在循环中构建查询字符串并将其作为准备好的语句执行。然而，这样做是不好的做法。相反，您应该避免为不同的酒精类别设置不同的列。使用具有多行的单列。每行将包含 jobid、酒精类别和一个值(例如 1 或 2)。

我有点不明白为什么你的列被称为 *_id，因为你似乎存储了一些是/否信息，而不是另一个表中某行的标识符。您可能希望选择一个误导性较小的名称。

对于类别，您可以使用枚举，或存储在字符串中的名称，或引用列出您的种类的其他一些表的主键的数字。 enum 意味着每个新类别都需要更改数据库架构。字符串可能会消耗大量内存，特别是如果您选择长名称。因此，引用另一个表是最灵活的解决方案，从长远来看可能是您的最佳选择。

您仍然可以基于扩展表将当前结构作为 View 提供。如果您为重组表使用新名称，并为该 View 重用当前名称，则可以保持与现有代码的向后兼容性。

小提示:MySQL 使用 0 或 1 来表示 bool 值。因此，您可以简单地编写 foo=bar 而不是编写 case when foo=bar then 1 else 0 end。这将使您的代码更短。 Otoh，乍一看它也会让人难以阅读，所以也许最好在某处就此用法发表评论。