我有一个包含以下字段的表
id sell_date
a1 27/7/2012
a1 26/7/2012
a1 24/7/2012
a1 24/2/2012
我想统计一下在当前日期之前一年内谁卖出了几个月。结果
a1 = 2
假设sell_date为date类型:
SELECT COUNT(DISTINCT MONTH(sell_date)) FROM `mytable`
WHERE id='a1'
AND sell_date>=DATE_SUB(NOW(),INTERVAL 1 YEAR);
此外,
删除id='a1' AND
将获得与任何id相关联的销售额(在上一年期间)的月数。如下所示:
SELECT COUNT(DISTINCT MONTH(sell_date)) FROM `mytable`
WHERE sell_date>=DATE_SUB(NOW(),INTERVAL 1 YEAR);
此外,
你可以得到每个id的列表,以及去年与该id相关的销售额的月数,如下所示:
SELECT COUNT(DISTINCT id,MONTH(sell_date)) FROM `mytable`
WHERE sell_date>=DATE_SUB(NOW(),INTERVAL 1 YEAR);
您需要datediff()函数:
select count(id) as N
from table_name
where datediff(curdate(), sell_date) >= 365;
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediffhttp://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_curdate