mysql - 如何每个月在mysql中每周获取数据

SELECT DATE_FORMAT(es.scheduled_datetime, '%X-%V') AS date,
    COUNT(es.event_schedule_id) AS total,
    0 as type
    FROM  event_schedule es ,event_schedule_mapping esm,events e
    WHERE
    es.event_schedule_id  = esm.event_schedule_id and
    esm.event_id = e.event_id and
    es.event_status_id in(1,2) and
    es.scheduled_datetime BETWEEN
    '2017-01-01' AND '2017-01-31'
    GROUP BY date
    ORDER BY date

这是我使用此查询的查询，我能够显示记录的计数大于零，并且输出如下

一月份

'2017-01', '2', '0'
'2017-02', '2', '0'
'2017-03', '10', '0'
'2017-04', '2', '0'
'2017-05', '9', '0'

但如果第二周计数为零，则无法显示，而我必须
 显示还：

'2017-01', '2', '0'
'2017-02', '0', '0'
'2017-03', '10', '0'
'2017-04', '2', '0'
'2017-05', '9', '0'

请建议我如何显示“记录”，如果计数为零，则应每周明智地建议我

最佳答案

如果您想每个月每周从数据库中获取数据，请查询该查询。

SELECT DISTINCT EXTRACT(WEEK FROM date) as week,tbl_data.date AS date, SUM(count.calories) AS sum,tbl_data.offset AS offset from tbl_data INNER JOIN count ON count.id = tbl_data.item_id where tbl_data.user_id="+user_id+" and tbl_data.date and  MONTH(date) = MONTH(CURRENT_DATE()) GROUP BY week

关于mysql - 如何每个月在mysql中每周获取数据，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42130967/