SQL查询按日期范畴折叠一再值

无论我们在那边有空,OP都但愿“to”月与“from”月沟通,以是我们只需行使coalesce：coalesce(last_am,am).因为last删除了任何空值,因此我们的毗连不必要是外毗连.

> select a.eid,b.m,b.y,c.m,c.y,a.v 
   from cm_result_data a 
    join cm_abs_month b 
      on ( a.eid = b.eid and a.am = b.am)  
    join cm_abs_month c 
      on ( a.eid = c.eid and 
      coalesce( a.last_am - 1,(select max(am) from cm_abs_month d where d.eid = a.eid )
      ) = c.am)
    order by 1,3,2,5,4;
+-----+------+------+------+------+------+
| eid | m    | y    | m    | y    | v    |
+-----+------+------+------+------+------+
| 100 |    1 | 2008 |    2 | 2008 |   80 |
| 100 |    3 | 2008 |    3 | 2008 |   90 |
| 100 |    4 | 2008 |    4 | 2008 |   80 |
| 200 |    1 | 2008 |    2 | 2008 |   80 |
| 200 |    3 | 2008 |    3 | 2008 |   90 |
| 200 |    4 | 2008 |    4 | 2008 |   80 |
+-----+------+------+------+------+------+

通过插手我们得到OP想要的输出.

不是说我们必需插手.可巧,我们的absolute_month函数是双向的,以是我们可以从头计较年份并从中抵消月份.

起首,让我们来处理赏罚添加“封顶”月份：

> create or replace view cm_capped_result as 
select eid,am,coalesce( 
   last_am - 1,(select max(b.am) from cm_abs_month b where b.eid = a.eid)
  ) as last_am,v  
 from cm_result_data a;

此刻我们获得按OP名目化的数据：

select eid,( (am - 1) % 12 ) + 1 as sm,floor( ( am - 1 ) / 12 ) as sy,( (last_am - 1) % 12 ) + 1 as em,floor( ( last_am - 1 ) / 12 ) as ey,v    
from cm_capped_result 
order by 1,4;

+-----+------+------+------+------+------+
| eid | sm   | sy   | em   | ey   | v    |
+-----+------+------+------+------+------+
| 100 |    1 | 2008 |    2 | 2008 |   80 |
| 100 |    3 | 2008 |    3 | 2008 |   90 |
| 100 |    4 | 2008 |    4 | 2008 |   80 |
| 200 |    1 | 2008 |    2 | 2008 |   80 |
| 200 |    3 | 2008 |    3 | 2008 |   90 |
| 200 |    4 | 2008 |    4 | 2008 |   80 |
+-----+------+------+------+------+------+

尚有OP想要的数据.全部SQL都应该在任何RDBMS上运行,而且被解析为简朴,易于领略且易于测试的视图.

是从头插手照旧从头计较？我会把这个(这是一个能力题目)留给读者.

(假如您的RDBMS不应承在视图中行使分组,则必需起首插手,然后分组或分组,然后行使相干子查询提取月份和年份.这留给读者操练.)

Jonathan Leffler在评述中问道,

What happens with your query if there
are gaps in the data (say there’s an
entry for 2007-12 with value 80,and
another for 2007-10,but not one for
2007-11? The question isn’t clear what
should happen there.

嗯,你是完全正确的,OP没有指明.大概存在(未说起的)条件前提,即没有间隙.在没有要求的环境下,我们不该该实行环绕也许不存在的对象举办编码.但究竟是,差距使得“插手”计谋失败;在这些前提下,“从头计较”计策不会失败.我会说更多,但这将显现我在上面提到的能力题目中的诀窍.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!