SQL查询：过去一年每个月销售额、卖出商品数和新增用户数（包含无数值月份）

好的，你需要使用LEFT JOIN语句将月份表连接到结果集中，这样即使没有数值也能显示月份。以下是更新后的三个SQL查询语句：

查询过去一年每个月的销售额：

SELECT 
    m.month,
    IFNULL(SUM(o.total), 0) AS sales_amount
FROM 
    (SELECT '01' AS month UNION SELECT '02' AS month UNION SELECT '03' AS month UNION SELECT '04' AS month UNION SELECT '05' AS month UNION SELECT '06' AS month UNION SELECT '07' AS month UNION SELECT '08' AS month UNION SELECT '09' AS month UNION SELECT '10' AS month UNION SELECT '11' AS month UNION SELECT '12' AS month) AS m
LEFT JOIN 
    orders o ON DATE_FORMAT(o.created_at, '%Y-%m') = m.month AND o.created_at BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND NOW()
GROUP BY 
    m.month;

查询过去一年每个月的卖出商品数：

SELECT 
    m.month,
    IFNULL(SUM(oi.quantity), 0) AS sold_items
FROM 
    (SELECT '01' AS month UNION SELECT '02' AS month UNION SELECT '03' AS month UNION SELECT '04' AS month UNION SELECT '05' AS month UNION SELECT '06' AS month UNION SELECT '07' AS month UNION SELECT '08' AS month UNION SELECT '09' AS month UNION SELECT '10' AS month UNION SELECT '11' AS month UNION SELECT '12' AS month) AS m
LEFT JOIN 
    orders o ON DATE_FORMAT(o.created_at, '%Y-%m') = m.month AND o.created_at BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND NOW()
INNER JOIN 
    order_items oi ON o.id = oi.order_id
GROUP BY 
    m.month;

查询过去一年每个月的新增用户数：

SELECT 
    m.month,
    IFNULL(COUNT(u.id), 0) AS new_users
FROM 
    (SELECT '01' AS month UNION SELECT '02' AS month UNION SELECT '03' AS month UNION SELECT '04' AS month UNION SELECT '05' AS month UNION SELECT '06' AS month UNION SELECT '07' AS month UNION SELECT '08' AS month UNION SELECT '09' AS month UNION SELECT '10' AS month UNION SELECT '11' AS month UNION SELECT '12' AS month) AS m
LEFT JOIN 
    users u ON DATE_FORMAT(u.created_at, '%Y-%m') = m.month AND u.created_at BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND NOW()
GROUP BY 
    m.month;