php - 棘手的mysql计算日期范围内每一天的出现次数

Question

我在获取以下记录时遇到问题

TABLE
id | holiday_From | holiday_To
1  | 2012-01-02   | 2012-01-03
1  | 2012-01-11   | 2012-01-16
1  | 2012-01-08   | 2012-01-22
1  | 2012-01-29   | 2012-01-30
1  | 2012-01-08   | 2012-01-11

我正在尝试获取给定月份的假期 - 即

BETWEEN "2012-01-01" AND "2012-01-31"

有一个类似的post但我无法根据需要调整它

RESULT
day (within range) | count() //number of ppl on holiday
DATE               | 3

例如

SAMPLE OUTPUT
2012-01-02 | 1
2012-01-03 | 1
2012-01-08 | 2
2012-01-09 | 2
2012-01-10 | 2
2012-01-11 | 3
2012-01-12 | 2
2012-01-13 | 2
2012-01-14 | 2
2012-01-15 | 2
2012-01-16 | 2
......

换句话说，我试图获取在特定日期找到记录的次数。 IE。 1号、2号、3号等有多少人放假

并非每个月的每一天都在表中

有什么想法吗？

附注这就是我已经拥有的(我在黑暗中拍摄的)

SELECT h.holiday_From, h.holiday_To, COUNT( * )
FROM holiday h
JOIN holiday ho ON h.holiday_From
BETWEEN DATE( "2012-01-01" )
AND IF( DATE( "2012-01-31" ) , DATE( "2012-01-31" ) , DATE( "2012-01-01" ) )
GROUP BY h.holiday_From, h.holiday_To

Answer 1

请不要害怕:))

基于 generate days from date range

select d.everyday, count(*) from (select @rownum:=@rownum+1, date('2012-01-01') + interval (@rownum-1) day everyday from
(select 0 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) t,
(select 0 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) t2,
(select 0 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) t3,
(SELECT @rownum:=0) r WHERE @rownum < DAY(LAST_DAY('2012-01-01'))) d, tablename tbl WHERE d.everyday>=tbl.hFrom AND d.everyday<tbl.hTo GROUP BY d.everyday

结果:

2012-01-02  1
2012-01-08  2
2012-01-09  2
2012-01-10  2
2012-01-11  2
2012-01-12  2
2012-01-13  2
2012-01-14  2
2012-01-15  2
2012-01-16  1
2012-01-17  1
2012-01-18  1
2012-01-19  1
2012-01-20  1
2012-01-21  1
2012-01-29  1

ps: 我将列重命名为 hFrom 和 hTo

pps:日期范围的更新变体

select d.everyday, count(*) from (select @rownum:=@rownum+1, date('2012-01-01') + interval (@rownum - 1) day everyday from
(select 0 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) t,
(select 0 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) t2,
(select 0 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) t3,
(SELECT @rownum:=0) r WHERE @rownum <= DATEDIFF('2012-01-11','2012-01-01')) d, `test` tbl WHERE d.everyday BETWEEN tbl.hFrom AND tbl.hTo GROUP BY d.everyday

已更新 - 所有联合中都缺少 2 号。它应该不会对任何事情产生重大影响。