请教, 不知可否用一条SQL语句实现,请高手赐教,不胜感激! 资料如下表所示:Table1
artic_code parts_code parts_name
10001 50001,50002,50003 部件一
10002 50001 部件二
10003 10001,10002 部件三1. parts_code 栏位,为50001或50001,50002,都是以5码递加,中间用","号隔开.
2. 如何用一句SQL,写成视图,将资料拉出来如下.
artic_code parts_code parts_name
10001 50001 部件一
10001 50002 部件一
10001 50003 部件一
10002 50001 部件二
10003 10001 部件三
10003 10002 部件三
artic_code parts_code parts_name
10001 50001,50002,50003 部件一
10002 50001 部件二
10003 10001,10002 部件三1. parts_code 栏位,为50001或50001,50002,都是以5码递加,中间用","号隔开.
2. 如何用一句SQL,写成视图,将资料拉出来如下.
artic_code parts_code parts_name
10001 50001 部件一
10001 50002 部件一
10001 50003 部件一
10002 50001 部件二
10003 10001 部件三
10003 10002 部件三
2 substr(parts_code,(rownum-1)*6+1,5),
3 parts_name from table1
4 where ARTIC_CODE ='10001'
5 connect by rownum <=(length(parts_code)+1)/6
6 union all
7 select ARTIC_CODE,
8 substr(parts_code,(rownum-1)*6+1,5),
9 parts_name from
10 (select * from table1 where ARTIC_CODE ='10002'
11 )
12 connect by rownum <=(length(parts_code)+1)/6
13 union all
14 select ARTIC_CODE,
15 substr(parts_code,(rownum-1)*6+1,5),
16 parts_name from
17 (select * from table1 where ARTIC_CODE ='10003'
18 )
19 connect by rownum <=(length(parts_code)+1)/6
20 /
ARTIC_CODE SUBSTR(PARTS_CODE,(ROWNUM-1)*6 PARTS_NAME
---------- ------------------------------ ----------
10001 50001 部件一
10001 50002 部件一
10001 50003 部件一
10002 50001 部件二
10003 10001 部件三
10003 10002 部件三
6 rows selected
Executed in 0.062 seconds
如果你的ARTIC_CODE很多的话 可以建个视图,从里面抽取ARTIC_CODE
将sql语句也用视图包装起来会好些
2 as
3 select ARTIC_CODE,
4 substr(parts_code,(rownum-1)*6+1,5) parts_code,
5 parts_name from table1
6 where ARTIC_CODE ='10001'
7 connect by rownum <=(length(parts_code)+1)/6
8 union all
9 select ARTIC_CODE,
10 substr(parts_code,(rownum-1)*6+1,5) parts_code,
11 parts_name from
12 (select * from table1 where ARTIC_CODE ='10002'
13 )
14 connect by rownum <=(length(parts_code)+1)/6
15 union all
16 select ARTIC_CODE,
17 substr(parts_code,(rownum-1)*6+1,5) parts_code,
18 parts_name from
19 (select * from table1 where ARTIC_CODE ='10003'
20 )
21 connect by rownum <=(length(parts_code)+1)/6
22 ;
View created
Executed in 0.125 seconds
SQL> select * from v1;
ARTIC_CODE PARTS_CODE PARTS_NAME
---------- ---------- ----------
10001 50001 部件一
10001 50002 部件一
10001 50003 部件一
10002 50001 部件二
10003 10001 部件三
10003 10002 部件三
6 rows selected
Executed in 0.063 seconds
这样就可以:
SQL> create or replace view v1
2 as
3 select distinct ARTIC_CODE,
4 substr(parts_code,(Level-1)*6+1,5) parts_code,
5 parts_name from table1
6 connect by Level <=(length(parts_code)+1)/6
7 order by ARTIC_CODE,parts_code
8 ;
View created
Executed in 0.125 seconds
SQL> select * from v1;
ARTIC_CODE PARTS_CODE PARTS_NAME
---------- ---------- ----------
10001 50001 部件一
10001 50002 部件一
10001 50003 部件一
10002 50001 部件二
10003 10001 部件三
10003 10002 部件三
6 rows selected
Executed in 0.063 seconds