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

(
    SELECT
        prod_name,
        IF(orders IS NULL,0,orders) as orders
    FROM
        Products as p
        LEFT JOIN (
            SELECT
                prod_id,
                count(order_num) as orders
            FROM
                OrderItems
            GROUP BY
                prod_id
        ) as l on p.prod_id = l.prod_id
)

UNION 

(
    SELECT
        prod_name,
        IF(orders IS NULL,0,orders)
    FROM
        Products as p 
        RIGHT JOIN (
            SELECT
                prod_id,
                count(order_num) as orders
            FROM
                OrderItems
            GROUP BY
                prod_id
        ) as r 
        on p.prod_id = r.prod_id
)
order by prod_name;