select id=row_number()over(order by (select 1)), a.*,b.BLU1,b.RepN1,c.BLU2,c.RepN2,bulT,RepNT from a left join b on CHARINDEX(a.BLU+' ',b.BLU1+' ')>0 left join c on CHARINDEX(a.BLU+' ',c.BLU1+' ')>0 left join d on d.RepNT=b.RepN1 or d.RepNT=c.RepN2
--> 测试数据[TB1] if object_id('[TB1]') is not null drop table [TB1] go create table [TB1]([BLU] nvarchar(6),[RepN] int) insert [TB1] select '01 08', 434 union all select '01 04', 477 union all select '07 12', 519 union all select '07 11', 549 union all select '08 11', 560 union all select '04 11', 568 union all select '01 07', 581 union all select '04 12', 598 union all select '01 06', 601 union all select '10 11', 609 union all select '06 10', 625 union all select '02 03', 634 union all select '06 08', 646 union all select '03 08', 652 --> 测试数据[TB2] if object_id('[TB2]') is not null drop table [TB2] go create table [TB2]([BLU1] nvarchar(4),[RepN1] int) insert [TB2] select '02',5655 union all select '03',25026 union all select '04',29502 union all select '05',89886 union all select '06',71380 union all select '07',109676 union all select '08',132257 union all select '09',201662 union all select '10',245659 union all select '11',140627 union all select '12',155814--> 测试数据[TB3] if object_id('[TB3]') is not null drop table [TB3] go create table [TB3]([BLU2] nvarchar(4),[RepN2] int) insert [TB3] select '01',133955 union all select '02',147485 union all select '03',153295 union all select '04',166689 union all select '05',188967 union all select '06',138090 union all select '07',107990 union all select '08',60633 union all select '09',68339 union all select '10',26799 union all select '11',14902 --> 测试数据[TB4] if object_id('[TB4]') is not null drop table [TB4] go create table [TB4]([BLUT] nvarchar(4),[RepNT] int) insert [TB4] select '1 ', 133955 union all select '2 ', 153140 union all select '11 ', 155529 union all select '12 ', 155814 union all select '3 ', 178321 union all select '8 ', 192890 union all select '4 ', 196191 union all select '6 ', 209470 union all select '7 ', 217666 union all select '9 ', 270001 union all select '10 ', 272458 union all select '5 ', 278853 --------------生成数据-------------------------- select id=row_number()over(order by (select 1)), a.*,b.BLU1,b.RepN1,c.BLU2,c.RepN2,d.bulT,d.RepNT from tb1 a left join tb2 b on CHARINDEX(a.BLU+' ',b.BLU1+' ')>0 left join tb3 c on CHARINDEX(a.BLU+' ',c.BLU2+' ')>0 left join tb4 d on d.RepNT=b.RepN1 or d.RepNT=c.RepN2
id=row_number()over(order by (select 1)),
a.*,b.BLU1,b.RepN1,c.BLU2,c.RepN2,bulT,RepNT
from a left join b on CHARINDEX(a.BLU+' ',b.BLU1+' ')>0
left join c on CHARINDEX(a.BLU+' ',c.BLU1+' ')>0
left join d on d.RepNT=b.RepN1 or d.RepNT=c.RepN2
if object_id('[TB1]') is not null drop table [TB1]
go
create table [TB1]([BLU] nvarchar(6),[RepN] int)
insert [TB1]
select '01 08', 434 union all
select '01 04', 477 union all
select '07 12', 519 union all
select '07 11', 549 union all
select '08 11', 560 union all
select '04 11', 568 union all
select '01 07', 581 union all
select '04 12', 598 union all
select '01 06', 601 union all
select '10 11', 609 union all
select '06 10', 625 union all
select '02 03', 634 union all
select '06 08', 646 union all
select '03 08', 652
--> 测试数据[TB2]
if object_id('[TB2]') is not null drop table [TB2]
go
create table [TB2]([BLU1] nvarchar(4),[RepN1] int)
insert [TB2]
select '02',5655 union all
select '03',25026 union all
select '04',29502 union all
select '05',89886 union all
select '06',71380 union all
select '07',109676 union all
select '08',132257 union all
select '09',201662 union all
select '10',245659 union all
select '11',140627 union all
select '12',155814--> 测试数据[TB3]
if object_id('[TB3]') is not null drop table [TB3]
go
create table [TB3]([BLU2] nvarchar(4),[RepN2] int)
insert [TB3]
select '01',133955 union all
select '02',147485 union all
select '03',153295 union all
select '04',166689 union all
select '05',188967 union all
select '06',138090 union all
select '07',107990 union all
select '08',60633 union all
select '09',68339 union all
select '10',26799 union all
select '11',14902
--> 测试数据[TB4]
if object_id('[TB4]') is not null drop table [TB4]
go
create table [TB4]([BLUT] nvarchar(4),[RepNT] int)
insert [TB4]
select '1 ', 133955 union all
select '2 ', 153140 union all
select '11 ', 155529 union all
select '12 ', 155814 union all
select '3 ', 178321 union all
select '8 ', 192890 union all
select '4 ', 196191 union all
select '6 ', 209470 union all
select '7 ', 217666 union all
select '9 ', 270001 union all
select '10 ', 272458 union all
select '5 ', 278853
--------------生成数据--------------------------
select
id=row_number()over(order by (select 1)),
a.*,b.BLU1,b.RepN1,c.BLU2,c.RepN2,d.bulT,d.RepNT
from tb1 a left join tb2 b on CHARINDEX(a.BLU+' ',b.BLU1+' ')>0
left join tb3 c on CHARINDEX(a.BLU+' ',c.BLU2+' ')>0
left join tb4 d on d.RepNT=b.RepN1 or d.RepNT=c.RepN2