mysql - 用零填充缺失的行

Question

我正在尝试获取数据库中特定项目的评级。并非所有项目都具有 1、2、3、4 或 5 的评级；发生这种情况时，如何用 0 填充该行？

因此，例如，当我有 1、2、3 的评分但没有 4 和 5 的评分时，我如何用 0 填充这两行？

select 
rating,
count(rating) as rating_count,
(count(rating) / (select count(item_id) from ratings where item_id = 3) * 100) as percent,
avg(rating)
from ratings 
where item_id = 3
group by rating desc
with rollup

这是上述查询的上述结果，如您所见，没有 1 和 2 评级，我如何获得 rating_count、percent 和 avg(rating) 是零吗？

@Hogan 的回答:

Answer 1

这就是我的工作(基于@Hogan 的原始查询):

select
r_list.r as rating,
ifnull(count(rating), 0) as rating_count,
ifnull((count(rating) / (select count(item_id) from ratings where item_id = ?) * 100), 0) as percent,
ifnull(avg(rating), 0)
from (SELECT 1 AS r
UNION ALL
    SELECT 2 AS r
UNION ALL
    SELECT 3 AS r
UNION ALL
    SELECT 4 AS r
UNION ALL
    SELECT 5 AS r
) r_list
left join ratings on r_list.r = ratings.rating and item_id = ?
group by r_list.r desc
with rollup