MySql 单表，选择最近 7 天并包含空行

Question

我在 stackoverflow 上搜索过类似的问题，但我不明白如何让它工作，我想做什么...

所以，我想从数据库中获取最近 7 天的交易并获取总销售额，如果某天没有数据，还包括空行。

我目前拥有的: http://sqlfiddle.com/#!2/f4eda/6

这个输出:

| PURCHASE_DATE | AMOUNT |
|---------------|--------|
|    2014-04-25 |     19 |
|    2014-04-24 |     38 |
|    2014-04-22 |     19 |
|    2014-04-19 |     19 |

我想要的:

| PURCHASE_DATE | AMOUNT |
|---------------|--------|
|    2014-04-25 |     19 |
|    2014-04-24 |     38 |
|    2014-04-23 |      0 |
|    2014-04-22 |     19 |
|    2014-04-21 |      0 |
|    2014-04-20 |      0 |
|    2014-04-19 |     19 |

感谢任何帮助:)

Answer 1

这并不容易。我从这个线程中得到了帮助 generate days from date range并将其与您的查询相结合。

因此，我们的想法是获取最近 7 天的日期列表，然后将这些日期与静态数量 0 连接到您的查询中，最后对它们求和。这可以用于任何日期范围，只需要在两个查询中更改它们

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

DEMO