如何在 MySQL 中检索最近 7 天销售行数为零的交易数据？

MySql 单表：选择最近 7 天的空行

问题：

目标是检索最近 7 天的交易数据并显示每个交易的总销售额天，包括没有交易的天的空行。以下查询仅返回销售额非零的天数：

SELECT PURCHASE_DATE, SUM(AMOUNT) AS AMOUNT
FROM transactions
WHERE PURCHASE_DATE BETWEEN NOW() - INTERVAL 7 DAY AND NOW()
GROUP BY PURCHASE_DATE;

解决方案：

要获得所需的结果，请使用查询组合：

1. 生成列表日期：

   SELECT DATE_FORMAT(a.Date,'%Y-%m-%d') AS purchase_date,
   '0' AS amount
   FROM (
     SELECT CURDATE() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Date
     FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
     CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
     CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
   ) a
   WHERE a.Date BETWEEN NOW() - INTERVAL 7 DAY AND NOW()

此查询生成过去 7 天的日期列表。

2. 加入交易数据：

   SELECT 
     t1.purchase_date,
     COALESCE(SUM(t1.amount + t2.amount), 0) AS amount
   FROM
     (SELECT DATE_FORMAT(a.Date,'%Y-%m-%d') AS purchase_date,
     '0' AS  amount
     FROM (
    SELECT CURDATE() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Date
    FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
     ) a
     WHERE a.Date BETWEEN NOW() - INTERVAL 7 DAY AND NOW()
   )t1
   LEFT JOIN
     (SELECT DATE_FORMAT(purchase_date, '%Y-%m-%d') AS purchase_date,
     COALESCE(SUM(amount), 0) AS amount
     FROM transactions
     WHERE purchase_date BETWEEN NOW() - INTERVAL 7 DAY AND NOW()
     AND vendor_id = 0
     GROUP BY purchase_date
   )t2
   ON t2.purchase_date = t1.purchase_date
   GROUP BY t1.purchase_date
   ORDER BY t1.purchase_date DESC;

此查询左连接生成的日期列表与实际交易数据，为没有交易的日子添加空行。合并用于处理空值。

以上是如何在 MySQL 中检索最近 7 天销售行数为零的交易数据？的详细内容。更多信息请关注PHP中文网其他相关文章！