题解 | #返回产品名称和每一项产品的总订单数#

# using函数
select t1.prod_name, count(t2.order_num) orders
from Products t1
left join OrderItems t2 using(prod_id)
group by t1.prod_name
order by t1.prod_name asc

# case函数
select t1.prod_name, (
    case
        when t2.orders is null then 0
        else t2.orders
    end
) orders
from Products t1
left join (
    select prod_id, count(order_num) orders
    from OrderItems
    group by prod_id
) t2 on t1.prod_id=t2.prod_id
order by t1.prod_name

# ifnull函数
select t1.prod_name, ifnull(t2.orders, 0)
from Products t1
left join (
    select prod_id, count(order_num) orders
    from OrderItems
    group by prod_id
) t2 on t1.prod_id=t2.prod_id
order by t1.prod_name