表A
13304
1330402
1330405 表B
133040101 10
133040102 11
133040201 12
133040202 21
133040501 12
133040502 32
结果:
13304 21
1330402 33
1330405 44求:如何得到上面的结果集?
13304
1330402
1330405 表B
133040101 10
133040102 11
133040201 12
133040202 21
133040501 12
133040502 32
结果:
13304 21
1330402 33
1330405 44求:如何得到上面的结果集?
SQL> with a(col1) as(
2 select 13304 from dual
3 union select 1330402 from dual
4 union select 1330405 from dual
5 ),
6 b(col1,col2) as(
7 select 133040101,10 from dual
8 union select 133040102,11 from dual
9 union select 133040201,12 from dual
10 union select 133040202,21 from dual
11 union select 133040501,12 from dual
12 union select 133040502,32 from dual
13 )
14 select a.col1,sum(b.col2) col2
15 from a left join b
16 on a.col1||decode(length(a.col1),5,'01','')=substr(b.col1,1,7)
17 group by a.col1; COL1 COL2
---------- ----------
13304 21
1330402 33
1330405 44
from a t, a t1, b t2
where t1.num like t.num || '%'
and t2.num not like t1.num || '%'
and t2.num like t.num || '%'
group by t.num
(select substr(b.col1,1,7)coll1,col2 from b)
group by coll1