我在获取以下记录时遇到了麻烦
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 1ps:我将列重命名为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计数
发布评论