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

两种方法，方法一用sum，但是要注意要用到cast转换函数，不可以用round，不过目前不清楚为什么不支持使用round

SELECT    
    Products.prod_name,    
    Cast(SUM(    
        CASE    
            WHEN OrderItems.order_num IS NOT NULL THEN 1    
            ELSE 0    
        END    
    ) as unsigned ) AS orders    
FROM    
    Products    
LEFT JOIN OrderItems ON Products.prod_id = OrderItems.prod_id    
GROUP BY    
    Products.prod_name    
ORDER BY    
    Products.prod_name

方法二，使用count聚合函数，因为如果值为NULL count函数默认为0，且count聚合函数不能选择count(*)因为有部分值为NULL

应该选择count(order_num)

SELECT
    Products.prod_name,
    count(OrderItems.order_num) AS orders
FROM
    Products
    LEFT JOIN OrderItems ON Products.prod_id = OrderItems.prod_id
GROUP BY
    Products.prod_name
ORDER BY
    Products.prod_name