mysql then end,MySQL：对多个内部联接表使用case when then end方法

I have a query which returns results using 'Case When Then End' from three tables which are joind. It looks like this:

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

This works fine, with each resulting-row giving eaither 1 or 0 correctly. The thing to note here is that each table has only one entry(row) for a specific "filename".

Now when I try to add another Inner Join with a table which can have multiple entries(rows) per "filename", the result becomes wrong in a way that only the last 'sum' shows the correct values whereas other 'sums' give wrong values. This second query is:

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

Any idea to get the right figures for all columns?

解决方案

Your one-to-many join is causing the overall row count to increase, such that when your value=1, you are adding that value more than once. To mitigate this, you may want to use the count function instead, using it to count distinct user IDs. Something like:

Count(distinct case when [logic goes here] then [user ID] else null end) as bikeuser

Then, you are only counting each user once rather than adding each row.