単一の MySQL テーブルに空の行を含む過去 7 日間のトランザクションを取得するにはどうすればよいですか?

過去 7 日間のトランザクションを取得し、MySQL 単一テーブルに空の行を含める

過去 7 日間のトランザクションを抽出するという課題に対処するためMySQL データベースから空の行をキャプチャするには、次の処理を実行します。アプローチ:

1. 日付範囲の生成:
   まず、次のクエリを使用して過去 7 日間にわたる日付のリストを作成します:

   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()

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

完全なクエリ:

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

この包括的な利用クエリを実行すると、取引のない日の空の行を効果的に含めて、過去 7 日間のそれぞれの合計売上高を抽出できます。

以上が単一の MySQL テーブルに空の行を含む過去 7 日間のトランザクションを取得するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。