设表A
入库日期 周期 单位
in_date period unit
2005-05-05 6 月
2006-03-06 3 月
2006-01-01 6 月
要求显示结果2005-12-31<in_date+period(unit)<2007-1-1的月份,只显示06年
即
2006-05,2006-11
2006-06,2006-09,2006-12
2006-07
入库日期 周期 单位
in_date period unit
2005-05-05 6 月
2006-03-06 3 月
2006-01-01 6 月
要求显示结果2005-12-31<in_date+period(unit)<2007-1-1的月份,只显示06年
即
2006-05,2006-11
2006-06,2006-09,2006-12
2006-07
解决方案 »
- oracle数据丢失问题
- 如何查某个用户执行exec procedure 的记录???
- 设置db_cache_size的问题
- $lnscrtl 命令无效
- java编程,将固定文本导入oracle问题,急
- 在线等!急!在linux下安装9i建立数据库的时候总是蹦出来"invalid entry size(expected 429924352 but got 429924342"?
- 为什么我在ORACLE中建表时没有timestamp类型?
- 哪里有学习pl/sql的好教程,高分相赠
- 哪儿有关于 oracle 语法的免费电子文档可以下载?
- 求教,可以在c#或java中使用call调用oracle存储过程并获取返回的结果集吗?
- 内存数据库的问题
- 如何在PL/SQL中用动态表名中查询数据
to_char(add_months(a.in_date,a.period*b.id),'yyyy-mm')
from
表A a,
(select 0 as id from dual union
select 1 from dual union
select 2 from dual union
select 3 from dual) b
where
add_months(a.in_date,a.period*b.id) between (to_date('2005-12-31,'yyyy-mm-dd') and to_date('2007-01-01','yyyy-mm-dd'))
2 temp_date date;
3 print_date varchar2(100) default '';
4 begin
5 for y in c_cursor loop
6 temp_date := y.in_date;
7 print_date := '';
8 loop
9 if temp_date > '2005-12-31' and temp_date < '2007-1-1' then
10 print_date := print_date||' '||to_char(temp_date,'yyyy-mm');
11 end if;
12 temp_date := add_months(temp_date,y.period_unit);
13 exit when temp_date >= '2007-1-1';
14 end loop;
15 dbms_output.put_line(print_date);
16 end loop;
17 end;
18 / 2006-05 2006-11
2006-03 2006-06 2006-09 2006-12
2006-01 2006-07PL/SQL procedure successfully completed
2 temp_date date;
3 print_date varchar2(100) default '';
4 begin
5 for y in c_cursor loop
6 temp_date := y.in_date;
7 print_date := '';
8 loop
9 if temp_date > '2005-12-31' and temp_date < '2007-1-1' then
10 print_date := print_date||' '||to_char(temp_date,'yyyy-mm');
11 end if;
12 select case y.unit when '月' then add_months(temp_date,y.period)
13 when '日' then temp_date + y.period
14 when '周' then temp_date + y.period*7
15 when '年' then add_months(temp_date,y.period*12)
16 else temp_date + y.period
17 end into temp_date from dual ;
18 exit when temp_date >= '2007-1-1';
19 end loop;
20 dbms_output.put_line(print_date);
21 end loop;
22 end;
23 / 2006-05 2006-11
2006-03 2006-06 2006-09 2006-12
2006-01 2006-07
2006-01PL/SQL procedure successfully completed
SQL> select sj,sx from a;SJ SX
----------- ----------
2005-5-5 6
2006-3-6 3
2006-1-1 6SQL> select sj "入库",substr(max(k),1,30) "验库"
2 from
3 (
4 select sj,substr(sys_connect_by_path(r,','),2) k
5 from
6 (
7 select sj,r,row_number()over(partition by sj order by r) rn
8 from
9 (
10 select sj,case when substr(sj+sx*(r-1),-2,2)>12 then substr(sj+sx*(r-1),1,4)+1 || substr(sj+sx*(r-1),-2,2)-12 else sj+sx*(r-1) end r--,row_number()over(partition by sj order by sj) rn
11 from
12 (select to_char(sj,'yyyymm') sj,sx,trunc(24/sx) x from a) m,
13 (select rownum r from dual connect by 1=1 and rownum <=12) n
14 where x>=r
15 order by sj,r
16 )
17 where r>='200601' and r <= '200612' and r<> sj
18 )
19 start with rn=1
20 connect by rn=prior rn+1 and sj=prior sj --and substr(r,1,4)='2006'
21 )
22 group by sj order by sj
23 ;入库 验库
------ ------------------------------
200505 200605,200611
200601 200607
200603 200606,200609,200612SQL>