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

AI: '是的，以上查询语句会返回过去一年内的12个月份的销售额、卖出商品数和新增用户数，即使某个月没有任何销售或新增也会被显示出来。每个查询语句使用了一个子查询来生成包含所有12个月份的日期表，然后使用LEFT JOIN将其连接到结果集中以确保每个月份都能被包含。如果你在查询中使用了自己的数据，可以在测试时先确认结果是否正确。'

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

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;