日期范围内每天的棘手mysql计数 [英] Tricky mysql count occurrences of each day within date range

问题描述

我在获取以下记录时遇到了麻烦

I'm having trouble with getting the records for the following

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

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

I'm trying to get occurrences of holidays for a given month - i.e.

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

有一个类似的

there is a similar post but im unable to tweak it for my needs

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号等有多少人在度假.

In other words I am trying to get how many times a record is found for a specific date. I.e. how many people are on holiday on the 1st, 2nd 3nd etc.

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

Not every day is in the TABLE for each month

有什么想法吗?

p.s.这就是我已经准备好的(我在黑暗中拍摄)

p.s. this is what i have already (my shot in the dark)

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

推荐答案

请不要害怕:))

基于从日期范围生成天

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

ps: I renamed columns to hFrom and hTo

pps:更新了日期范围的变体

pps: updated variant for the range of dates

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号.它不会对任何事物产生重大影响.

Updated - number 2 was missing from all the unions. it should not significantly affect anything.

这篇关于日期范围内每天的棘手mysql计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！