日期范围内每天的棘手mysql计数

本文介绍了日期范围内每天的棘手mysql计数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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.