现有一个科目代码表t,有如下字段:KMDM(科目代码)、SJKM(上级科目)
数据例如以下:
KMDM SJKM
A A
A1 A
A2 A
A11 A1
A12 A1
A111 A11
A21 A2
A22 A2
如何用SQL语句实现:将各个科目代码所包含的下级科目列出来。
即按上例子,希望得到的结果为:
科目代码 下级科目
A A
A A1
A A2
A A11
A A12
A A21
A A22
A A111
A1 A11
A1 A12
A1 A111
A11 A111
A2 A21
A2 A22
数据例如以下:
KMDM SJKM
A A
A1 A
A2 A
A11 A1
A12 A1
A111 A11
A21 A2
A22 A2
如何用SQL语句实现:将各个科目代码所包含的下级科目列出来。
即按上例子,希望得到的结果为:
科目代码 下级科目
A A
A A1
A A2
A A11
A A12
A A21
A A22
A A111
A1 A11
A1 A12
A1 A111
A11 A111
A2 A21
A2 A22
貌似你表的设计就有问题...
B AS (SELECT 'A1',kmdm from t where t.kmdm like 'A1%'),
C AS (SELECT 'A11',kmdm from t where t.kmdm like 'A11%'),
D AS (SELECT 'A2',kmdm from t where t.kmdm like 'A2%')
SELECT * FROM A UNION ALL
SELECT * FROM B UNION ALL
SELECT * FROM C UNION ALL
SELECT * FROM D
from t
where kmdm = sjkm
union all
select connect_by_root(sjkm), kmdm
from (select * from t where kmdm <> sjkm)
connect by nocycle prior kmdm = sjkm
2 select 'A' kmdm,'A' sjkm from dual union all
3 select 'A1' kmdm,'A' sjkm from dual union all
4 select 'A2' kmdm,'A' sjkm from dual union all
5 select 'A11' kmdm,'A1' sjkm from dual union all
6 select 'A12' kmdm,'A1' sjkm from dual union all
7 select 'A111' kmdm,'A11' sjkm from dual union all
8 select 'A21' kmdm,'A2' sjkm from dual union all
9 select 'A22' kmdm,'A2' sjkm from dual)
10 select kmdm, sjkm
11 from t
12 where kmdm = sjkm
13 union all
14 select connect_by_root(sjkm), kmdm
15 from (select * from t where kmdm <> sjkm)
16 connect by nocycle prior kmdm = sjkm
17 /
KMDM SJKM
---- ----
A A
A A1
A A11
A A111
A A12
A A2
A A21
A A22
A1 A11
A1 A111
A1 A12
A11 A111
A2 A21
A2 A22
14 rows selected
from t
where kmdm = sjkm
union all
select connect_by_root(sjkm), kmdm
from (select * from t where kmdm <> sjkm)
connect by nocycle prior kmdm = sjkm