MySQL:对多个内部联接表使用 Case When Then End

Question

我有一个查询，它使用“Case When Then End”从连接的三个表中返回结果。它看起来像这样:

select f.filename, 
sum(case when v.rUser like '%bike%' then 1 else 0 end) as bikeUser, 
sum(case when v.rUser like '%Pedestrian%' then 1 else 0 end) as pedestrianUser, 
sum(case when d.weather like '%clear%' then 1 else 0 end) as clearWeather
from VMdata v 
inner join files f on v.id = f.id
inner join DMdata d on f.id = d.id
where f.filename in (X,Y,Z)
group by f.filename 

这很好用，每个结果行都正确地给出了 1 或 0。这里要注意的是，每个表对于特定的“文件名”只有一个条目(行)。现在，当我尝试使用每个“文件名”可以有多个条目(行)的表添加另一个内部连接时，结果变得错误，只有最后一个“总和”显示正确的值，而其他“总和”给出错误值。第二个查询是:

select f.filename, 
sum(case when v.rUser like '%bike%' then 1 else 0 end) as bikeUser,
sum(case when v.rUser like '%Pedestrian%' then 1 else 0 end) as pedestrianUser, 
sum(case when d.weather like '%clear%' then 1 else 0 end) as clearWeather,
sum(case when m.extras like '%hat%' then 1 else 0 end) as hatExtras
from VMdata v 
inner join files f on v.id = f.id
inner join DMdata d on f.id = d.id
inner join MultiFiledata m on f.id = m.id
where f.filename in (X,Y,Z)
group by f.filename

是否知道要为所有列获取正确的数字？

Answer 1

如果 MultiFiledata 表可以包含 files 表中任何相应记录的多条记录，那么您需要在单独的查询中进行聚合并加入回到主查询。

例如(语法可能不完美；专注于概念):

select   f.filename, 
         sum(case when v.rUser like '%bike%' 
                  then 1 else 0 end) as bikeUser,
         sum(case when v.rUser like '%Pedestrian%' 
                  then 1 else 0 end) as pedestrianUser, 
         sum(case when d.weather like '%clear%' 
                  then 1 else 0 end) as clearWeather
from     VMdata v 
         inner join files f 
         on v.id = f.id
         inner join DMdata d 
         on f.id = d.id
         inner join (
             select id, 
                    sum(case when extras like '%hat%' 
                             then 1 else 0 end) as hatExtras
             from   MultiFiledata 
         ) m 
         on f.id = m.id
where    f.filename in (X,Y,Z)
group by f.filename;