使用mysql间隔获取按月分组的数据 [英] fetch data grouped by month using mysql interval
问题描述
我想要按月分组的采购表中过去 1 年的记录.重要的是要提到最近两个月没有购买,包括当前.mysql查询如下
I want records from last 1 year from my purchase table grouped by month. its important to mention there is no purchase in last two month including current. mysql query is like following
SELECT Year(purchasedate) as y ,Month(purchasedate) as m, Count(*) FROM purchase WHERE productid=1001 AND purchasedate >= CURDATE() - INTERVAL 1 YEAR GROUP BY m, y
返回自上次购买日期起 11 个月的数据
it return 11 months data from last purchase date
2015 1 9177
2015 2 9914
2015 3 11672
2015 4 10521
2015 5 11873
2015 6 10449
2015 7 4057
2014 9 340
2014 10 9913
2014 11 8451
2014 12 7857
但它应该返回 12 个月的数据.
but it should return 12 months data.
2015 1 9177
2015 2 9914
2015 3 11672
2015 4 10521
2015 5 11873
2015 6 10449
2015 7 4057
2015 8 0
2015 9 0
2014 10 9913
2014 11 8451
2014 12 7857
我做错了什么.提前致谢.
What I am doing wrong. thanks in advance.
推荐答案
试试这个
SELECT d.y, d.m, p.cnt
FROM
(SELECT a.y, b.m
FROM (SELECT 2015 AS Y UNION ALL SELECT 2014 AS Y) a
CROSS JOIN (SELECT 1 AS m UNION ALL SELECT 2 AS m UNION ALL SELECT 3 AS m UNION ALL SELECT 4 AS m UNION ALL SELECT 5 AS m UNION ALL SELECT 6 AS m UNION ALL SELECT 7 AS m UNION ALL SELECT 8 AS m UNION ALL SELECT 9 AS m UNION ALL SELECT 10 AS m UNION ALL SELECT 11 AS m UNION ALL SELECT 12 AS m) b
WHERE CONCAT(a.y, '-', b.m, '-01') BETWEEN CURDATE() - INTERVAL 13 MONTH AND CURDATE() - INTERVAL 1 MONTH
ORDER BY 1, 2) d
LEFT OUTER JOIN
(SELECT YEAR(purchasedate) AS Y ,MONTH(purchasedate) AS m, COUNT(*) cnt FROM purchase WHERE productid=1001 AND purchasedate >= CURDATE() - INTERVAL 1 YEAR GROUP BY m, Y) p ON d.m=p.m AND d.y=p.y
ORDER BY 1 DESC, 2
您没有做错任何事,但 MySQL 无法返回数据集中不存在的月份的行.要解决此问题,您需要提供您自己的所有月份(此查询中的表 d
)和 LEFT JOIN
他们与您的数据集的结果
You are not doing anything wrong, but MySQL cannot return rows for months that do not exist in your data-set. To solve this problem, you need to provide all months your self (table d
in this query) and LEFT JOIN
them with results from your data-set
这篇关于使用mysql间隔获取按月分组的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!