Bagaimana untuk Mendapatkan Data Transaksi 7 Hari Terakhir dengan Baris Sifar Jualan dalam MySQL?

MySql Single Table: Memilih 7 Hari Terakhir dengan Baris Kosong

Masalah:

The objektif adalah untuk mendapatkan semula data transaksi dari 7 hari yang lalu dan memaparkan jumlah jualan untuk setiap hari, termasuk baris kosong berhari-hari tanpa sebarang transaksi. Pertanyaan berikut kembali hanya beberapa hari dengan jualan bukan sifar:

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

Penyelesaian:

Untuk mencapai hasil yang diinginkan, gabungan pertanyaan digunakan:

1. Jana Senarai Tarikh:

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

Pertanyaan ini menjana senarai tarikh dari 7 hari lepas.

2. Sertai dengan Data Transaksi:

   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;

Ini pertanyaan kiri menyertai senarai tarikh yang dijana dengan data transaksi sebenar, menambah baris kosong untuk hari tanpa transaksi. Penyatuan digunakan untuk mengendalikan nilai nol.

Atas ialah kandungan terperinci Bagaimana untuk Mendapatkan Data Transaksi 7 Hari Terakhir dengan Baris Sifar Jualan dalam MySQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!