MySql 单表，选择过去 7 天并包含空行

2021-11-20 00:00:00 sql mysql

我在 stackoverflow 上搜索过类似的问题，但我不明白如何进行这项工作，我正在尝试做什么...

I have searched similar problems here on stackoverflow but I could not understand how to make this work, what I'm trying to do...

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

So, I want to get last 7 days transactions from database and get total sales amount and also include empty rows if there is no data for some day.

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

What I have so far: 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 |

感谢任何帮助:)

推荐答案

只需将子查询与您想要的日期放在一起并使用 left outer join:

Simply put together a subquery with the dates you want and use left outer join:

select d.thedate, coalesce(SUM(amount), 0) AS amount
from (select date('2014-04-25') as thedate union all
      select date('2014-04-24') union all
      select date('2014-04-23') union all
      select date('2014-04-22') union all
      select date('2014-04-21') union all
      select date('2014-04-20') union all
      select date('2014-04-19')
     ) d left outer join
     transactions t
     on t.purchase_date = d.thedate and vendor_id = 0
GROUP BY d.thedate
ORDER BY d.thedate DESC;

相关文章