sql问题求教

显示全部楼层 · 2009-10-9 08:28:00

表结构是这样的
lv startend
100190001 290001
200190001 290001
300190001 290001
400190001 290001
500190001 290001
600201101 290001
700201201 290001
800201101 290001
900201201 290001
我想要通过sql达到的结果是如果start的前4位小于2010则取值 2010 否则取原始值， end 前4位和当前年份比较，如果大于当前年份则取当前年份，通过语句想将 start与end 之间每年的值都列出来，达到如下效果
lvnd
100 2010
100 2011
100 2012
100 2013
100 2014
200 2010
......
900 2012
900 2013
900 2013
我的sql是这样的，遇到的问题就是为什么在lv=100的时候多跑出来几十行数据
with a as (select 100 aaa100,'190001' aae030,'290001' aae031 from dual
union all
select 100 aaa100,'190001' aae030,'290001' aae031 from dual
union all
select 200 aaa100,'190001' aae030,'290001' aae031 from dual
union all
select 300 aaa100,'190001' aae030,'290001' aae031 from dual
union all
select 400 aaa100,'190001' aae030,'290001' aae031 from dual
union all
select 500 aaa100,'190001' aae030,'290001' aae031 from dual
union all
select 600 aaa100,'201101' aae030,'290001' aae031 from dual
union all
select 700 aaa100,'201201' aae030,'290001' aae031 from dual
union all
select 800 aaa100,'201201' aae030,'290001' aae031 from dual
union all
select 900 aaa100,'201301' aae030,'290001' aae031 from dual
),
b as (select aaa100,greatest(substr(aae030,1,4),'2010') aae030, least(substr(aae031,1,4),to_char(trunc(sysdate,'yyyy'),'yyyy')) aae031
from a)
select aaa100,aae030+level-1 from b
where aae030+level-1 between aae030 and aae031 connect by PRIORaaa100= aaa100 and level <=(aae031-aae030+1) AND PRIOR SYS_GUID() IS NOT NULL

千问 · 2009-10-9 08:28:00

因为你构造的数据有两行100:
with a as (select 100 aaa100,'190001' aae030,'290001' aae031 from dual
union all
select 100 aaa100,'190001' aae030,'290001' aae031 from dual

千问 · 2009-10-9 08:28:00

。。。。。这没注意粘贴多了谢谢newkid

千问 · 2009-10-9 08:28:00

newkid 发表于 2014-11-19 05:40
因为你构造的数据有两行100:
with a as (select 100 aaa100,'190001' aae030,'290001' aae031 from dual ...
另外再问个问题，为什么我把a 建成实表然后用同样的语句跑就报错 ora-01436
create table a (lv varchar2(6),aae030 varchar2(6),aae031 varchar2(6));
insert into a
select (level-1)*100+100,'2010','2014'from dual connect by level <=10;
select lv, aae030 + level - 1
from a
where aae030 + level - 1 between aae030 and aae031
connect by PRIOR lv = lv
and level <= (aae031 - aae030 + 1)
AND PRIOR SYS_GUID() IS NOT NULL;

千问 · 2009-10-9 08:28:00

connect by NOCYCLE PRIOR lv = lv

千问 · 2009-10-9 08:28:00

lastwinner 发表于 2014-11-19 10:36
connect by NOCYCLE PRIOR lv = lv
这个我试了，加上以后结果就不对了加上后出来的结果只有10行我用的是 oracle10g 这个跟版本有关系么？

千问 · 2009-10-9 08:28:00

lastwinner 发表于 2014-11-19 10:36
connect by NOCYCLE PRIOR lv = lv
刚做了测试在11g上这么跑不会报错。。
create table a (lv varchar2(6),aae030 varchar2(6),aae031 varchar2(6));
insert into a
select (level-1)*100+100,'2010','2014'from dual connect by level <=10;
select lv, aae030 + level - 1
from a
where aae030 + level - 1 between aae030 and aae031
connect by PRIOR lv = lv
and level <= (aae031 - aae030 + 1)
AND PRIOR SYS_GUID() IS NOT NULL;

千问 · 2009-10-9 08:28:00

57楼说的两回事

千问 · 2009-10-9 08:28:00

starfifi 发表于 2014-11-19 10:46
这个我试了，加上以后结果就不对了加上后出来的结果只有10行我用的是 oracle10g 这个跟版本有关系么？
那就别用这种方法，再构造一个集合去做笛卡尔积。