MySQL获取一个月中每一天某个日期的行数 [英] MySQL to get the count of rows that fall on a date for each day of a month

问题描述

我有一个表，其中包含社区事件列表，其中包含事件开始和结束的日子的列.如果结束日期为0，则事件仅在开始日期发生.我有一个查询，它返回在任何给定的一天发生的事件数:

I have a table that contains a list of community events with columns for the days the event starts and ends. If the end date is 0 then the event occurs only on the start day. I have a query that returns the number of events happening on any given day:

SELECT COUNT(*) FROM p_community e WHERE 
    (TO_DAYS(e.date_ends)=0 AND DATE(e.date_starts)=DATE('2009-05-13')) OR
    (DATE('2009-05-13')>=DATE(e.date_starts) AND DATE('2009-05-13')<=DATE(e.date_ends))

我只是想在任何要测试"2009-05-13"的日期中进行输入.

I just sub in any date I want to test for "2009-05-13".

我需要能够在整个月的每一天中获取此数据.我可以一次对每天运行一次查询，但是我宁愿运行一次可以一次给我整个月的查询.有人对我该怎么做有什么建议吗?

I need to be be able to fetch this data for every day in an entire month. I could just run the query against each day one at a time, but I'd rather run one query that can give me the entire month at once. Does anyone have any suggestions on how I might do that?

不，我不能使用存储过程.

And no, I can't use a stored procedure.

推荐答案

尝试:

SELECT COUNT(*), DATE(date) FROM table WHERE DATE(dtCreatedAt) >= DATE('2009-03-01') AND DATE(dtCreatedAt) <= DATE('2009-03-10') GROUP BY DATE(date);

这将获得2009年5月每天的金额.

This would get the amount for each day in may 2009.

已更新:现在可以在跨月/年的日期范围内使用.

UPDATED: Now works on a range of dates spanning months/years.

这篇关于MySQL获取一个月中每一天某个日期的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！