我有这个表结构:
编辑更复杂的例子:添加隐藏范围
category| day | a | --------|------------|-------| 1 | 2012-01-01 | 4 | 1 | 2012-01-02 | 4 | 1 | 2012-01-03 | 4 | 1 | 2012-01-04 | 4 | 1 | 2012-01-05 | 5 | 1 | 2012-01-06 | 5 | 1 | 2012-01-07 | 5 | 1 | 2012-01-08 | 4 | 1 | 2012-01-09 | 4 | 1 | 2012-01-10 | 4 | 1 | 2012-01-11 | 5 | 1 | 2012-01-12 | 5 | 1 | 2012-01-16 | 5 | 1 | 2012-01-17 | 5 | 1 | 2012-01-18 | 5 | 1 | 2012-01-19 | 5 | ...
‘category-day’作为唯一键.我会根据列“a”和给定的限制范围为每个类别提取一系列日期,如下所示:
1,2012-01-01|2012-01-04,4 1,2012-01-05|2012-01-07,5 1,2012-01-08|2012-01-10,2012-01-11|2012-01-12,2012-01-13|2012-01-15,0 1,2012-01-16|2012-01-19,5
或类似的.
我搜索最好的方法来做到这一点.最好只用MysqL而且还带一点点PHP.
注1:不是全天都插入:两天之间非连续性不可能是其他日子.在这种情况下,我将输出错过的范围,列“a”= 0.
注意2:我用一个简单的查询和一些PHP的行来做,但我不喜欢它,因为我的简单算法需要一个周期,每个范围的范围乘以每个类别找到.如果范围太大而且类别太多,那就不太好了.
最终编辑:好的!阅读完所有评论和答案后,我认为不存在有效,高效且同时可读的解决方案.所以Mosty Mostacho的答案是100%有效的解决方案,但它有100%有效的建议.谢谢你们.
新编辑:
正如我在评论中告诉你的那样,我强烈建议你使用快速查询,然后在PHP中处理缺少的日期,因为它会更快,更易读:
select concat(@category := category,',min(day)) col1,concat(max(day),@a := a) col2 from t,(select @category := '',@a := '',@counter := 0) init where @counter := @counter + (category != @category or a != @a) group by @counter,category,a
但是,如果您仍想使用查询版本,请尝试以下操作:
select @counter := @counter + (category != @category or a != @a) counter,concat(@category := category,@a := a) col2 from ( select distinct s.day,s.category,coalesce(t1.a,0) a from ( select (select min(day) from t) + interval val - 1 day day,c.category from seq s,(select distinct category from t) c having day <= (select max(day) from t) ) s left join t t1 on s.day = t1.day and s.category = t1.category where s.day between ( select min(day) from t t2 where s.category = t2.category) and ( select max(day) from t t2 where s.category = t2.category) order by s.category,s.day ) t,@counter := 0) init group by counter,a order by category,min(day)
请注意,MysqL不会允许你动态创建数据,除非你硬编码UNIONS,为example.这是一个昂贵的过程,这就是为什么我强烈建议你创建一个只有整数字段的表,其值从1到X,其中X是,至少是从表中分隔最小(日)和最大(日)的最大日期数.如果您不确定该日期,只需添加100,000个数字,您就可以生成超过200年的测距期.在上一个查询中,此表是seq,它具有的列是val.
这导致:
+--------------+--------------+ | COL1 | COL2 | +--------------+--------------+ | 1,2012-01-01 | 2012-01-04,4 | | 1,2012-01-05 | 2012-01-07,5 | | 1,2012-01-08 | 2012-01-10,2012-01-11 | 2012-01-12,2012-01-13 | 2012-01-15,0 | | 1,2012-01-16 | 2012-01-19,5 | +--------------+--------------+