题解 | #返回产品名称和每一项产品的总订单数#
返回产品名称和每一项产品的总订单数
http://www.nowcoder.com/practice/1c64fd9048364a58a8ffa541720359a4
SELECT a.prod_name,count(b.order_num) orders
FROM Products a
left join OrderItems b on a.prod_id=b.prod_id
group by a.prod_name
union
SELECT a.prod_name,count(b.order_num) orders
FROM Products a
left join OrderItems b on a.prod_id=b.prod_id
where a.prod_id is null
group by a.prod_name
order by prod_name
FROM Products a
left join OrderItems b on a.prod_id=b.prod_id
group by a.prod_name
union
SELECT a.prod_name,count(b.order_num) orders
FROM Products a
left join OrderItems b on a.prod_id=b.prod_id
where a.prod_id is null
group by a.prod_name
order by prod_name