现有两张数据表
tblA
id name sjid
01 A 0
02 B 0
0101 C 01
0102 D 01
0103 E 01
010101 F 0101
010102 G 0101
0201 H 02
020101 G 0201
03 H 0
0301 I 03
04 J 0
tblB
ZH id
test 010101
test 020101
请问如何通过这两张表产生以下结构的数据id name sjid
01 A 0
0101 C 01
010101 E 0101
02 B 0
0201 H 02
020101 G 0201
tblA
id name sjid
01 A 0
02 B 0
0101 C 01
0102 D 01
0103 E 01
010101 F 0101
010102 G 0101
0201 H 02
020101 G 0201
03 H 0
0301 I 03
04 J 0
tblB
ZH id
test 010101
test 020101
请问如何通过这两张表产生以下结构的数据id name sjid
01 A 0
0101 C 01
010101 E 0101
02 B 0
0201 H 02
020101 G 0201
order by lpad(id,8,'0');
where substr(id,1,2) in(select substr(id,1,2) from tblb t2 )
order by lpad(id,8,'0');
SQL>
SQL> with tblA as
2 (
3 select '01' id, 'A' name, '0' sjid from dual union all
4 select '02' id, 'B' name, '0' sjid from dual union all
5 select '0101' id, 'C' name, '01' sjid from dual union all
6 select '0102' id, 'D' name, '01' sjid from dual union all
7 select '0103' id, 'E' name, '01' sjid from dual union all
8 select '010101' id, 'F' name, '0101' sjid from dual union all
9 select '010102' id, 'G' name, '0101' sjid from dual union all
10 select '0201' id, 'H' name, '02' sjid from dual union all
11 select '020101' id, 'G' name, '0201' sjid from dual union all
12 select '03' id, 'H' name, '0 sjid' from dual union all
13 select '0301' id, 'I' name, '03' sjid from dual union all
14 select '04' id, 'J' name, '0' sjid from dual
15 )
16 ,
17 tblB as
18 (
19 select 'test' zh, '010101' id from dual union all
20 select 'test' zh, '020101' id from dual
21 )
22 select m.*
23 from (select t.*
24 from tblA t
25 start with t.sjid = '0'
26 connect by prior t.id = t.sjid) m,
27 tblB n
28 where m.id in (select substr(n.id, 0, rownum * 2)
29 from dual
30 connect by rownum <= length(n.id) / 2)
31 ;ID NAME SJID
------ ---- ------
01 A 0
0101 C 01
010101 F 0101
02 B 0
0201 H 02
020101 G 02016 rows selectedSQL>
SELECT '01' ID, 'A' NAME, '0' SJID FROM DUAL UNION ALL
SELECT '02', 'B', '0' FROM DUAL UNION ALL
SELECT '0101', 'C', '01' FROM DUAL UNION ALL
SELECT '0102', 'D', '01' FROM DUAL UNION ALL
SELECT '0103', 'E', '01' FROM DUAL UNION ALL
SELECT '010101', 'F', '0101' FROM DUAL UNION ALL
SELECT '010102', 'G', '0101' FROM DUAL UNION ALL
SELECT '0201', 'H', '02' FROM DUAL UNION ALL
SELECT '020101', 'G', '0201' FROM DUAL UNION ALL
SELECT '03', 'H', '0' FROM DUAL UNION ALL
SELECT '0301', 'I', '03' FROM DUAL UNION ALL
SELECT '04', 'J', '0' FROM DUAL
),
tab2 as (
select 'test' zh, '010101' id from dual union all
select 'test' zh, '020101' id from dual
)
SELECT ID,NAME,SJID
FROM tab1
WHERE id IN(
SELECT DISTINCT SubStr(id, 0, level)
FROM tab2
CONNECT BY LEVEL <= Length(id)
)
START WITH SJID='0'
CONNECT BY PRIOR ID=SJID
id name sjid
--------------------------------
01 A 0
0101 C 01
010101 E 0101
02 B 0
0201 H 02
020101 G 0201
from tb1 t
start with t.id in (select a.id from tb1 a,tb2 b
where a.id=b.id)
connect by t.id= prior t.sjid
order by id
SQL> with tb1 as
2 (select '01' id, 'A' name, '0' sjid from dual union all
3 select '02' id, 'B' name, '0' sjid from dual union all
4 select '0101' id, 'C' name, '01' sjid from dual union all
5 select '0102' id, 'D' name, '01' sjid from dual union all
6 select '0103' id, 'E' name, '01' sjid from dual union all
7 select '010101' id, 'F' name, '0101' sjid from dual union all
8 select '010102' id, 'G' name, '0101' sjid from dual union all
9 select '0201' id, 'H' name, '02' sjid from dual union all
10 select '020101' id, 'G' name, '0201' sjid from dual union all
11 select '03' id, 'H' name, '0 sjid' from dual union all
12 select '0301' id, 'I' name, '03' sjid from dual union all
13 select '04' id, 'J' name, '0' sjid from dual),
14 tb2 as(select 'test' zh, '010101' id from dual union all
15 select 'test' zh, '020101' id from dual
16 )
17 select t.id,t.name,t.sjid
18 from tb1 t
19 start with t.id in (select a.id from tb1 a,tb2 b
20 where a.id=b.id)
21 connect by t.id= prior t.sjid
22 order by id
23 /
ID NAME SJID
------ ---- ------
01 A 0
0101 C 01
010101 F 0101
02 B 0
0201 H 02
020101 G 0201
6 rows selected
SQL>
id name sjid
01 A 0
0101 C 01
010101 E 0101 --是否写错了 应是f
02 B 0
0201 H 02
020101 G 0201
id name sjid
--------------------------------
01 A 0
0101 C 01
010101 E 0101 --怎么来的 e hehe
02 B 0
0201 H 02
020101 G 0201
我晕,我SQL运行的结果没copy下来,我还要去我SQLTOOLS里面复制我运行的结果比较麻烦,看YY_MM_DD已经有那个结果了,就直接复制YY_MM_DD上面的结果,哈哈O(∩_∩)O哈哈~
你眼睛真尖