SELECT prod_name,IF(orders IS NULL,0,orders) AS orders
FROM Products AS p 
LEFT JOIN (SELECT prod_id,COUNT(*) AS orders FROM OrderItems GROUP BY prod_id) AS oi
ON p.prod_id = oi.prod_id
ORDER BY prod_name;

首先确定使用左连接，然后筛选不同商品id的总数，连接条件是id相同，统计数量使用count（注意null需要更改否则不统计） 最后排序即可