MySQL查询求助：指定StoreId无数据时如何返回0.0

需求：对StoreId为(1,2,3)的数据进行求和计算，当StoreId不存在、对应数据为0或null时，仍返回0.0。

原查询语句：

select s.location, temp3.c from (
    select temp1.StoreId, 100*temp2.vis/temp1.pass as c from
    (select sum(passerBy) pass, StoreId from TemporaryAriadneData
    where StoreId in (${StoreId})
    And DATE_ADD(ariadne_date , INTERVAL 2 HOUR) between '${start_date}' AND '${end_date}'
    group by StoreId) temp1
    inner JOIN
    (select sum(visitor) vis, StoreId from
    (select *, str_to_date(CONCAT( CONCAT( date, ' '), time), '%Y-%m-%d %H:%i:%s') AS date_time from ReportAriadneMaps
    where StoreId in (${StoreId})) as temp
    WHERE date_time between '${start_date}' AND '${end_date}'
    group by StoreId) temp2
    on temp1.StoreId = temp2.StoreId) temp3
    left JOIN Stores s
    on s.id = temp3.StoreId
    order by location;

当前查询结果 #

仅返回存在有效统计数据的StoreId对应的location和计算值，无数据的StoreId条目直接缺失。

期望查询结果 #

所有指定的StoreId都显示对应的location，无数据的条目计算值为0.0。

补充：@gotqn提供的解决方案结果（StoreId=(9,11,12)） #

所有指定StoreId均显示，无数据的条目计算值为0.0，符合需求。

解决方案 #

原查询的核心问题是inner join会过滤掉无匹配的StoreId，且子查询仅返回有数据的StoreId，导致最终结果缺失条目。调整思路是先基于指定的StoreId列表构建完整的基础数据集，再左连接统计结果，用COALESCE处理null值：

-- 先构造包含所有指定StoreId的临时表
WITH target_stores AS (
    SELECT id AS StoreId FROM Stores WHERE id IN (${StoreId})
    -- 如果Stores表中可能不存在指定的StoreId，改用UNION ALL手动构造：
    -- SELECT 1 AS StoreId UNION ALL SELECT 2 UNION ALL SELECT 3
),
-- 统计passerBy的子查询，保留所有指定StoreId，无数据则sum为null
temp1 AS (
    SELECT ts.StoreId, SUM(tad.passerBy) AS pass
    FROM target_stores ts
    LEFT JOIN TemporaryAriadneData tad 
        ON ts.StoreId = tad.StoreId
        AND DATE_ADD(tad.ariadne_date, INTERVAL 2 HOUR) BETWEEN '${start_date}' AND '${end_date}'
    GROUP BY ts.StoreId
),
-- 统计visitor的子查询，同样保留所有指定StoreId
temp2 AS (
    SELECT ts.StoreId, SUM(ram.visitor) AS vis
    FROM target_stores ts
    LEFT JOIN (
        SELECT *, STR_TO_DATE(CONCAT(date, ' ', time), '%Y-%m-%d %H:%i:%s') AS date_time 
        FROM ReportAriadneMaps
        WHERE StoreId IN (${StoreId})
    ) ram 
        ON ts.StoreId = ram.StoreId
        AND ram.date_time BETWEEN '${start_date}' AND '${end_date}'
    GROUP BY ts.StoreId
)
-- 左连接两个统计结果，用COALESCE将null转为0，计算最终值
SELECT s.location, 
       COALESCE(100 * COALESCE(temp2.vis, 0) / COALESCE(temp1.pass, 1), 0.0) AS c
FROM target_stores ts
LEFT JOIN temp1 ON ts.StoreId = temp1.StoreId
LEFT JOIN temp2 ON ts.StoreId = temp2.StoreId
LEFT JOIN Stores s ON ts.StoreId = s.id
ORDER BY s.location;

关键调整说明：

1. target_stores临时表：确保所有指定的StoreId都被包含，即使Stores表中不存在（可改用UNION ALL手动构造）。
2. 左连接统计子查询：替代原有的inner join，保留所有StoreId，无数据时sum结果为null。
3. COALESCE函数：将null的统计值转为0，避免计算时出现null；分母为0时（pass为0），用COALESCE兜底返回0.0。
4. 避免提前过滤：将时间条件放到join的on子句中，而不是where子句，确保无数据的StoreId仍能保留。

内容的提问来源于stack exchange，提问作者Muhammad Haekal