MySQL-如何显示特定月份的全天记录? [英] MySQL - How to show all days records in particular month?

问题描述

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a 
where a.Date between '2015-04-15' and '2015-05-15' ORDER BY Date

以上查询工作正常，我从选定日期到选定日期获得30条记录.但我想显示完整的月份记录.如果我选择February，则将显示所有28天(如果为leap年则为29)记录.与Mar = 31记录相同April = 30记录等

Above query working fine I am getting 30 records from selected date to selected date. But I want to show complete month record. If i choose February then all 28 days(29 if leap year) record will showing. Same as Mar = 31 records April = 30 records. etc.

:查看屏幕截图.我想显示一个月中的所有日子.

EDIT : See screenshot. I want to show all days in a month.

如果可以使用PHP，请发表您的答案.

If possible to PHP please post your answer.

推荐答案

如果您知道输入的年份和月份，则可以始终设置第一天，例如输入为Y = 2012 M=02，则第一天将始终为，然后使用该日期，您可以获取最后一天，然后是该范围内的日期.就像

If you know your input year and month then you can always set the first day, say the input is Y = 2012 M=02 ,the first day would be always 2012-02-01 and using that date you can get the last day and then the dates in that range. Something as

select a.Date 
from (
    select last_day('2012-02-01') - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a 
where a.Date between '2012-02-01' and last_day('2012-02-01') order by a.Date;

+------------+
| Date       |
+------------+
| 2012-02-01 |
| 2012-02-02 |
| 2012-02-03 |
| 2012-02-04 |
| 2012-02-05 |
| 2012-02-06 |
| 2012-02-07 |
| 2012-02-08 |
| 2012-02-09 |
| 2012-02-10 |
| 2012-02-11 |
| 2012-02-12 |
| 2012-02-13 |
| 2012-02-14 |
| 2012-02-15 |
| 2012-02-16 |
| 2012-02-17 |
| 2012-02-18 |
| 2012-02-19 |
| 2012-02-20 |
| 2012-02-21 |
| 2012-02-22 |
| 2012-02-23 |
| 2012-02-24 |
| 2012-02-25 |
| 2012-02-26 |
| 2012-02-27 |
| 2012-02-28 |
| 2012-02-29 |
+------------+
29 rows in set (0.00 sec)

这篇关于MySQL-如何显示特定月份的全天记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！