mysql - 在 mysql 中循环我数如果

Question

我有名称为 pooling_station 和 wind_turbine 的 2 个表，并且在 wind_turbine 中存在 turbine_name。我想获取由多个 turbine_id 组成的所有涡轮机的数据，例如 SWSMHD-SC1-RWE01-G100 到 SWSMHD-SC1-RWE01-G112

为此我正在使用这个查询

select turbine name,
       count(if(turbine_name = 'SWSMHD-SC1-RWE01-G112', 1, NULL)) as turbine_count,
from scada_data 
where
   local_tm BETWEEN '2017-11-01' AND '2017-11-01' 
   and turbine_name in 
   (
      SELECT
         uniqueid 
      FROM
         wind_turbine 
      WHERE
         pooling_station = 20 
   );

但在此我必须为每个涡轮机输入 turbine_name。

如何在一次查询中获取所有 12 个涡轮机数据？

Answer 1

只需使用in 和group by:

select turbine_name, count(*) as turbine_count,
from scada_data 
where turbine_name in ('SWSMHD-SC1-RWE01-G112', . . . )
      local_tm BETWEEN '2017-11-01' AND '2017-11-01' and
      turbine_name in (select uniqueid from wind_turbine where pooling_station = 20 )
group by turbine_name;

。 . . 用于您的 turbine_name 列表。

如果您想要所有匹配的涡轮机，只需省略 in 条件:

select turbine_name, count(*) as turbine_count,
from scada_data 
where local_tm BETWEEN '2017-11-01' AND '2017-11-01' and
      turbine_name in (select uniqueid from wind_turbine where pooling_station = 20 )
group by turbine_name;