php - Mysql获取当月的结果

Question

我正在尝试将 mysql 的结果转换为 php 结果，持续 1 个月，每天，如果行不存在，则显示 0 到该日期，如下所示

2017-09-07 - 70
2017-09-10 - 0
2017-09-11 - 100
2017-09-12 - 0
2017-09-15 - 0
2017-09-20 - 0
2017-09-29 - 200

我的表名称是 transactions，包括 date、id 和 credits 字段。我尝试了下面在网上找到的代码，但只显示了 1 行，我在其中尝试对两个日期 1 和 26 进行两次检索以进行测试。

SELECT MonthDate.Date, COALESCE(SUM(`credits`), 0) FROM ( SELECT 1 AS Date UNION ALL SELECT 26) AS MonthDate LEFT JOIN transactions AS T1 ON MonthDate.Date = DAY(T1.Date) AND MONTH(T1.Date) = 9 AND YEAR(T1.Date) = 2017 WHERE MonthDate.Date <= DAY(LAST_DAY('2017-09-28'))

Answer 1

可以用SQL实现

我们建议您有一个包含字段“value”和“date”的表“your_table”。然后 SQL 将如下所示:

select a.Date, IFNULL(your_table.value, 0)
from (
  select curdate() + INTERVAL 31 DAY - 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
left join your_table on your_table.date = a.Date
where a.Date between 'PUT_START_DATE_HERE' and 'PUT_END_DATE_HERE'
order by a.Date;