MySQL更新category表统计分类及子分类商品数时a.id未知报错解决

我需要更新category表，存储关联到该父分类及其下属子分类的所有商品总数。

最开始我编写了如下SQL语句：

update category as a 
set nb_items = (select count(distinct item_id) from (
        select item_id from item_category where category_id = a.id
        union all
        select item_category.item_id
        from category as sub_cat
        inner join item_category
            on item_category.category_id = sub_cat.id
        where sub_cat.parent_id = a.id
    ) as tmp
)
where category.parent_id is null

执行时触发报错，提示a.id未知。我可以用临时方案实现需求，但希望仅通过单条SQL语句完成，不使用临时表。

更新：最终可用方案 #

根据@Akina的建议，我调整出了如下查询，目前运行正常：

update category as a
inner join (
    select count(distinct item_id) as total, category_id as cat
    from item_category
    group by cat
    union all
    select count(distinct item_category.item_id) as total, top_cat.id as cat
    from category as top_cat
    inner join category as sub_cat
        on sub_cat.parent_id = top_cat.id
    inner join item_category
        on item_category.category_id = sub_cat.id
    group by cat
) as tmp
on tmp.cat = a.id
set a.nb_items = tmp.total
where a.parent_id is null;

目前无法100%确认所有场景下都能正确去重，但现有测试数据的运行结果符合预期。

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