MySQL报表数据缺失排查：Phone类周四订单量显示异常问题

数据表 #

Orders表 #

Items表 #

期望报表结果 #

问题描述 #

编写的SQL查询大部分结果正确，但Phone分类的周四（Thursday）订购数量应为1，查询却返回0。该数据在cte2中能正确获取，但在cte3中丢失。

错误分析 #

问题出在cte1的日期名称定义上：

• select "Thursday " union和select "Saturday " union这两行的字符串末尾多了一个空格，而cte2中通过dayname(order_date)返回的日期名称是无空格的（比如Thursday）。
• 当cte1和cte2进行left join时，带空格的Thursday 无法和无空格的Thursday匹配，导致这部分数据关联失败，coalesce(cte2.tt_qty, 0)返回0。
• 最终在case when判断时，cte3.day_name ='Thursday'也无法匹配到cte1中带空格的Thursday ，所以结果显示为0。

修正后的SQL查询 #

with cte1 as 
    (select * from
        (select "Monday" as day_name union 
        select "Tuesday" union
        select "Wednesday" union
        select "Thursday" union
        select "Friday" union
        select "Saturday" union
        select "Sunday" ) t1
     cross join
        (select distinct item_category from items) t2),
cte2 as 
    (select i.item_category, 
        dayname(order_date) as day_name,
        sum(quantity) as tt_qty
    from orders o
    inner join items i 
    on i.item_id  = o.item_id 
    group by i.item_category, dayname(order_date)),
cte3 as
    (select cte1.day_name,
        cte1.item_category,
        coalesce(cte2.tt_qty, 0) as tt_qty
    from cte1
    left join cte2
    on cte2.day_name = cte1.day_name
    and cte2.item_category = cte1.item_category)
select item_category as category, 
    coalesce (max(case when cte3.day_name ='Monday' then cte3.tt_qty 
        end),0) as Monday,
    coalesce (max(case when cte3.day_name ='Tuesday' then cte3.tt_qty 
        end),0) as Tuesday,
    coalesce (max(case when cte3.day_name ='Wednesday' then cte3.tt_qty 
        end),0) as Wednesday,
    coalesce (max(case when cte3.day_name ='Thursday' then cte3.tt_qty
        end),0) as Thursday,
    coalesce (max(case when cte3.day_name ='Friday' then cte3.tt_qty 
        end),0) as Friday,
    coalesce (max(case when cte3.day_name ='Saturday' then cte3.tt_qty 
        end),0) as Saturday,
    coalesce (max(case when cte3.day_name ='Sunday' then cte3.tt_qty 
        end),0) as Sunday       
from cte3
group by item_category
order by 1

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