表结构是这样的
编号 年度 7月 8月 9月 ...... 来年6月 等14个字段该表编号和年度是是主键。7月至来年6月 这12个字段里存的是代码值(0,1,2)现在想查找这样一个结果: 代码为2的开始月和代码为2的结束月例如:这样的数据
7月 8月 9月 10月 11月 12月 1月 2月 3月 4月 5月 6月
0 1 1 1 2 2 2 2 2 1 1 1结果是 开始年月为11月 结束年月为来年3月不可能有两个分开的,连续为2的时间段。
编号 年度 7月 8月 9月 ...... 来年6月 等14个字段该表编号和年度是是主键。7月至来年6月 这12个字段里存的是代码值(0,1,2)现在想查找这样一个结果: 代码为2的开始月和代码为2的结束月例如:这样的数据
7月 8月 9月 10月 11月 12月 1月 2月 3月 4月 5月 6月
0 1 1 1 2 2 2 2 2 1 1 1结果是 开始年月为11月 结束年月为来年3月不可能有两个分开的,连续为2的时间段。
SQL> create table t1(
2 id number,
3 year char(4),
4 M7 number(1),
5 M8 number(1),
6 M9 number(1),
7 M10 number(1),
8 M11 number(1),
9 M12 number(1),
10 M01 number(1),
11 M02 number(1),
12 M03 number(1),
13 M04 number(1),
14 M05 number(1),
15 M06 number(1)
16 );表已创建。SQL>
SQL> insert into t1 values(1,'2009',0,1,1,1,2,2,2,2,2,1,1,1);已创建 1 行。SQL> insert into t1 values(1,'2009',0,1,1,1,1,1,1,1,2,1,1,1);已创建 1 行。SQL> insert into t1 values(1,'2009',0,1,1,1,1,1,1,1,1,1,1,1);已创建 1 行。SQL> commit;提交完成。SQL>
SQL> select * from t1; ID YEAR M7 M8 M9 M10 M11 M12 M01 M02 M03 M04 M05 M06
---------- -------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 2009 0 1 1 1 2 2 2 2 2 1 1 1
1 2009 0 1 1 1 1 1 1 1 2 1 1 1
1 2009 0 1 1 1 1 1 1 1 1 1 1 1SQL>
SQL> select '开始年月为'||substr(t.res,1,instr(t.res,',',1)-1)||' 结束年月为'||(case when instr(t.res,',',-2)>0 then
2 replace(substr(t.res,instr(t.res,',',-2)+1),',','') else replace(t.res,',','') end)
3 as times from
4 (select trim(decode(M7,2,'M7,','')||decode(M8,2,'M8,','')||decode(M9,2,'M9,','')||decode(M10,2,'M10,','')||decode(M11,2,'M11,','')||decode(M12,2,'M12,','')
5 ||decode(M01,2,'M01,','')||decode(M02,2,'M02,','')||decode(M03,2,'M03,','')||decode(M04,2,'M04,','')||decode(M05,2,'M05,','')||decode(M06,2,'M06,','')) as res
6 from t1)t
7 where length(t.res)>0;TIMES
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
开始年月为M11 结束年月为M03
开始年月为M03 结束年月为M03SQL>select '开始年月为'||substr(t.res,1,instr(t.res,',',1)-1)||' 结束年月为'||(case when instr(t.res,',',-2)>0 then
replace(substr(t.res,instr(t.res,',',-2)+1),',','') else replace(t.res,',','') end)
as times from
(select trim(decode(M7,2,'M7,','')||decode(M8,2,'M8,','')||decode(M9,2,'M9,','')||decode(M10,2,'M10,','')||decode(M11,2,'M11,','')||decode(M12,2,'M12,','')
||decode(M01,2,'M01,','')||decode(M02,2,'M02,','')||decode(M03,2,'M03,','')||decode(M04,2,'M04,','')||decode(M05,2,'M05,','')||decode(M06,2,'M06,','')) as res
from t1)t
where length(t.res)>0;
SQL> select t.id,t.year,'开始年月为'||substr(t.res,1,instr(t.res,',',1)-1)||' 结束年月为'||(case when instr(t.res,',',-2)>0 then
2 replace(substr(t.res,instr(t.res,',',-2)+1),',','') else replace(t.res,',','') end)
3 as times from
4 (select id,year,trim(decode(M7,2,'M7,','')||decode(M8,2,'M8,','')||decode(M9,2,'M9,','')||decode(M10,2,'M10,','')||decode(M11,2,'M11,','')||decode(M12,2,'M12,','')
5 ||decode(M01,2,'M01,','')||decode(M02,2,'M02,','')||decode(M03,2,'M03,','')||decode(M04,2,'M04,','')||decode(M05,2,'M05,','')||decode(M06,2,'M06,','')) as res
6 from t1)t
7 where length(t.res)>0; ID YEAR TIMES
---------- -------- ------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
1 2009 开始年月为M11 结束年月为M03
1 2009 开始年月为M03 结束年月为M03