SQL 查询过去一年每个月的销售额、商品数和新增用户数

人：'帮我改一下下面的 SQL 语句，要求能显示出对应的月份

**AI：**好的，以下是更新后的三个 SQL 查询语句，用于查询从当前时间往前一年内每个月的销售额、卖出商品数和新增用户数，即使该月没有任何销售或新增也会被显示出来：

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

SELECT 
    DATE_FORMAT(date_table.month, '%Y-%m') AS month,
    IFNULL(SUM(total), 0) AS sales_amount
FROM 
    (SELECT 
        DATE_FORMAT((CURDATE() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) MONTH), '%Y-%m') AS month
    FROM 
        (SELECT 0 AS a UNION SELECT 1 AS a UNION SELECT 2 AS a UNION SELECT 3 AS a UNION SELECT 4 AS a UNION SELECT 5 AS a UNION SELECT 6 AS a UNION SELECT 7 AS a UNION SELECT 8 AS a UNION SELECT 9 AS a) AS a
    CROSS JOIN 
        (SELECT 0 AS a UNION SELECT 1 AS a UNION SELECT 2 AS a UNION SELECT 3 AS a UNION SELECT 4 AS a UNION SELECT 5 AS a UNION SELECT 6 AS a UNION SELECT 7 AS a UNION SELECT 8 AS a UNION SELECT 9 AS a) AS b
    CROSS JOIN 
        (SELECT 0 AS a UNION SELECT 1 AS a UNION SELECT 2 AS a UNION SELECT 3 AS a UNION SELECT 4 AS a UNION SELECT 5 AS a UNION SELECT 6 AS a UNION SELECT 7 AS a UNION SELECT 8 AS a UNION SELECT 9 AS a) AS c) date_table
LEFT JOIN 
    orders ON DATE_FORMAT(created_at, '%Y-%m') = date_table.month
        AND created_at BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND NOW()
GROUP BY 
    month;

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

SELECT 
    DATE_FORMAT(date_table.month, '%Y-%m') AS month,
    IFNULL(SUM(oi.quantity), 0) AS sold_items
FROM 
    (SELECT 
        DATE_FORMAT((CURDATE() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) MONTH), '%Y-%m') AS month
    FROM 
        (SELECT 0 AS a UNION SELECT 1 AS a UNION SELECT 2 AS a UNION SELECT 3 AS a UNION SELECT 4 AS a UNION SELECT 5 AS a UNION SELECT 6 AS a UNION SELECT 7 AS a UNION SELECT 8 AS a UNION SELECT 9 AS a) AS a
    CROSS JOIN 
        (SELECT 0 AS a UNION SELECT 1 AS a UNION SELECT 2 AS a UNION SELECT 3 AS a UNION SELECT 4 AS a UNION SELECT 5 AS a UNION SELECT 6 AS a UNION SELECT 7 AS a UNION SELECT 8 AS a UNION SELECT 9 AS a) AS b
    CROSS JOIN 
        (SELECT 0 AS a UNION SELECT 1 AS a UNION SELECT 2 AS a UNION SELECT 3 AS a UNION SELECT 4 AS a UNION SELECT 5 AS a UNION SELECT 6 AS a UNION SELECT 7 AS a UNION SELECT 8 AS a UNION SELECT 9 AS a) AS c) date_table
LEFT JOIN 
    orders o ON DATE_FORMAT(o.created_at, '%Y-%m') = date_table.month
        AND o.created_at BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND NOW()
LEFT JOIN 
    order_items oi ON o.id = oi.order_id
GROUP BY 
    month;

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

SELECT 
    DATE_FORMAT(date_table.month, '%Y-%m') AS month,
    IFNULL(COUNT(u.id), 0) AS new_users
FROM 
    (SELECT 
        DATE_FORMAT((CURDATE() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) MONTH), '%Y-%m') AS month
    FROM 
        (SELECT 0 AS a UNION SELECT 1 AS a UNION SELECT 2 AS a UNION SELECT 3 AS a UNION SELECT 4 AS a UNION SELECT 5 AS a UNION SELECT 6 AS a UNION SELECT 7 AS a UNION SELECT 8 AS a UNION SELECT 9 AS a) AS a
    CROSS JOIN 
        (SELECT 0 AS a UNION SELECT 1 AS a UNION SELECT 2 AS a UNION SELECT 3 AS a UNION SELECT 4 AS a UNION SELECT 5 AS a UNION SELECT 6 AS a UNION SELECT 7 AS a UNION SELECT 8 AS a UNION SELECT 9 AS a) AS b
    CROSS JOIN 
        (SELECT 0 AS a UNION SELECT 1 AS a UNION SELECT 2 AS a UNION SELECT 3 AS a UNION SELECT 4 AS a UNION SELECT 5 AS a UNION SELECT 6 AS a UNION SELECT 7 AS a UNION SELECT 8 AS a UNION SELECT 9 AS a) AS c) date_table
LEFT JOIN 
    users u ON DATE_FORMAT(u.created_at, '%Y-%m') = date_table.month
        AND u.created_at BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND NOW()
GROUP BY 
    month;

这些 SQL 语句使用了一个子查询 date_table 来生成过去一年每个月的月份，然后使用 LEFT JOIN 将其与 orders、order_items 和 users 表连接，最后使用 IFNULL 函数将没有销售记录或新增用户的月份的销售额、卖出商品数和新增用户数设置为 0。

希望这些信息对您有所帮助！