17,377
社区成员
发帖
与我相关
我的任务
分享
select max(checkdate),min(p.checkdate),to_char(p.checkdate,'yyyy-mm'),sum(checkmeters),flag
from (
select a.checksheetid,a.checkdate,a.checkmeters,case when to_number(to_char(a.checkdate,'dd'))>=26 then 1 else 0 end flag
from chk_sfpchecksh a
where a.checkdate>to_date('2009-10-25','yyyy-mm-dd')
order by a.checkdate
) p
group by to_char(p.checkdate,'yyyy-mm'),flag
<table width="295" border="0">
<tr>
<td>2009-10-31</td>
<td>2009-10-26</td>
<td>2009-10</td>
<td>103858.1</td>
<td>1</td>
</tr>
<tr>
<td>2009-11-25</td>
<td>2009-11-1</td>
<td>2009-11</td>
<td>362690.8</td>
<td>0</td>
</tr>
<tr>
<td>2009-11-30</td>
<td>2009-11-26</td>
<td>2009-11</td>
<td>63674</td>
<td>1</td>
</tr>
<tr>
<td>2009-12-25</td>
<td>2009-12-1</td>
<td>2009-12</td>
<td>321208.6</td>
<td>0</td>
</tr>
<tr>
<td>2009-12-31</td>
<td>2009-12-26</td>
<td>2009-12</td>
<td>97628.5</td>
<td>1</td>
</tr>
<tr>
<td>2010-1-24</td>
<td>2010-1-1</td>
<td>2010-01</td>
<td>368241.6</td>
<td>0</td>
</tr>
</table>
select max(checkdate),min(a.checkdate),to_char(add_months(a.checkdate-25,1),'yyyy-mm'),sum(checkmeters)
from chk_sfpchecksh a
where a.checkdate>to_date('2009-10-25','yyyy-mm-dd')
group by to_char(add_months(a.checkdate-25,1),'yyyy-mm')
order by 3;