！！！急～～高手帮忙来解决，真难啊！-_-b

[lboss_li]

设表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

[caflin]

难，等待高手ing......

[子陌红尘]

select
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'))

[xiaoxiao1984]

SQL> declare cursor c_cursor is select in_date, period_unit from test_a;
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-07

PL/SQL procedure successfully completed

[manyroads]

光考虑月份还不够的，周期的单位有可能是周，日之类。

[lboss_li]

高手啊，能查出来了，
可是我想把查出来的多条记录显示在一条记录里面，
怎么办啊？

[xiaoxiao1984]

SQL> declare cursor c_cursor is select in_date, period,unit from test_a;
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-01

PL/SQL procedure successfully completed

[xiaoxiao1984]

没有看懂楼主在说什么
要求不能一次提完么

[feng2]

写成一条语句够麻烦的，花了我40分钟！！
SQL> select sj,sx from a;

SJ SX
----------- ----------
2005-5-5 6
2006-3-6 3
2006-1-1 6

SQL> 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,200612

SQL>

[lboss_li]

牛人啊，接分！