mysql - 我想使用 MySQL 获取当年的销售额

Question

我想获得给定年份的以下结果

month | sales
jan     0.00
feb     4.00
mar     0.00
apr     45.00
.
.
.
.
dec    0.00

我的表是“sales”，它有 salse_value、sales_date 作为列。

问题是如何在 MYSQL 中使用 casewhen 语句得到上述结果

Answer 1

您需要生成所有月份名称并将其与表名称连接起来，这样没有记录的月份的总销售额将为零

SELECT  a.MonthName, COALESCE(SUM(b.sales_value), 0) totalSales
FROM    
        (
            SELECT 'Jan' AS monthName, 1 monthOrder
            UNION
            SELECT 'Feb' AS monthName, 2 monthOrder
            UNION
            SELECT 'Mar' AS monthName, 3 monthOrder
            UNION
            SELECT 'Apr' AS monthName, 4 monthOrder
            UNION
            SELECT 'May' AS monthName, 5 monthOrder
            UNION
            SELECT 'Jun' AS monthName, 6 monthOrder
            UNION
            SELECT 'Jul' AS monthName, 7 monthOrder
            UNION
            SELECT 'Aug' AS monthName, 8 monthOrder
            UNION
            SELECT 'Sep' AS monthName, 9 monthOrder
            UNION
            SELECT 'Oct' AS monthName, 10 monthOrder
            UNION
            SELECT 'Nov' AS monthName, 11 monthOrder
            UNION
            SELECT 'Dec' AS monthName, 12 monthOrder
        )   a LEFT JOIN sales b
                ON a.monthName = b.monthName
GROUP BY a.monthName
ORDER BY a.MonthOrder ASC