with department (departid, departname) as
(
select 'a', 'BOSCH' union all
select 'a001', 'GEM' union all
select 'a001001', 'T1' union all
select 'a001001001', 'T11' union all
select 'a001001001001', 'T111' union all
select 'a002', 'CHN' union all
select 'a002001', 'D1' union all
select 'a002001001', 'D11' union all
select 'a002001001001', 'D111' union all
select 'a003', 'AMR' union all
select 'a003001', 'M1' union all
select 'a003001001', 'M11' union all
select 'a003001001001', 'M111'
)
, worker(departid, gh, name) as
(
select 'a001' , 1001, 'zs' union all
select 'a001001001', 1002, 'ls' union all
select 'a002001' , 2001, 'ww' union all
select 'a003001001', 3001, 'zl'
)
select
worker.departid,
bm1.departname as BM1,
bm2.departname as BM2,
bm3.departname as BM3,
bm4.departname as BM4,
worker.gh,
worker.name
from worker
left outer join department bm1 on bm1.departid = SUBSTRING(worker.departid, 1,1) and LEN(worker.departid) >=1
left outer join department bm2 on bm2.departid = SUBSTRING(worker.departid, 1,4) and LEN(worker.departid) >=4
left outer join department bm3 on bm3.departid = SUBSTRING(worker.departid, 1,7) and LEN(worker.departid) >=7
left outer join department bm4 on bm4.departid = SUBSTRING(worker.departid, 1,10) and LEN(worker.departid) >=10
(
select 'a', 'BOSCH' union all
select 'a001', 'GEM' union all
select 'a001001', 'T1' union all
select 'a001001001', 'T11' union all
select 'a001001001001', 'T111' union all
select 'a002', 'CHN' union all
select 'a002001', 'D1' union all
select 'a002001001', 'D11' union all
select 'a002001001001', 'D111' union all
select 'a003', 'AMR' union all
select 'a003001', 'M1' union all
select 'a003001001', 'M11' union all
select 'a003001001001', 'M111'
)
, worker(departid, gh, name) as
(
select 'a001' , 1001, 'zs' union all
select 'a001001001', 1002, 'ls' union all
select 'a002001' , 2001, 'ww' union all
select 'a003001001', 3001, 'zl'
)
select
worker.departid,
bm1.departname as BM1,
bm2.departname as BM2,
bm3.departname as BM3,
bm4.departname as BM4,
worker.gh,
worker.name
from worker
left outer join department bm1 on bm1.departid = SUBSTRING(worker.departid, 1,1) and LEN(worker.departid) >=1
left outer join department bm2 on bm2.departid = SUBSTRING(worker.departid, 1,4) and LEN(worker.departid) >=4
left outer join department bm3 on bm3.departid = SUBSTRING(worker.departid, 1,7) and LEN(worker.departid) >=7
left outer join department bm4 on bm4.departid = SUBSTRING(worker.departid, 1,10) and LEN(worker.departid) >=10
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货