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;

このクエリは、生成された日付リストと実際のトランザクション データを左結合し、トランザクションのない日には空の行を追加します。合体は null 値を処理するために使用されます。

以上がMySQL で売上行がゼロの過去 7 日間のトランザクション データを取得する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。