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

Question

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'

请建议我如何显示记录，如果计数为零，应该每周请建议我

Answer 1

如果您想从数据库中每周获取数据，我有查询，请尝试此查询。

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