110,571
社区成员
发帖
与我相关
我的任务
分享
if object_id('a表') is not null
drop table a表
create table a表(tt varchar(6)) --a表
insert into a表 select '123456' union all
select '234567' union all
select '345678' union all
select '456789'
if object_id('b表') is not null
drop table b表
create table b表(t1 varchar(50),t2 varchar(50)) --b表
insert into b表 select '022123456',null union all
select null,'022234567' union all
select '033345678',null union all
select null,'044456789'
if object_id('新表') is not null
drop table 新表
create table 新表(tt varchar(6)) --新表
delete from 新表
--插入语句
insert into 新表
select t4.tt from a表 inner join
(select case when charindex('022',t)=1 then substring(t,4,6) else substring(t,1,6) end as tt
from
(select case when t1 is null then t2 when t2 is null then t1 end as t from b表)t3)t4
on a表.tt=t4.tt
select * from 新表 --查看结果
--结果
--tt
123456
234567
SELECT a.tt, nb.nt
FROM a INNER JOIN
(
SELECT (CASE SUBSTRING(ISNULL(t1, '') + t2, 1, 3)
WHEN '022' THEN SUBSTRING(ISNULL(t1, '') + t2, 4, 6)
ELSE SUBSTRING(ISNULL(t1, '') + t2, 1, 6) END) AS nt
FROM b
) AS nb ON a.tt = nb.nt