有两个表
tb1CODE
1A
3Atb2
TYPE1 TYPE2 TYPE_CODE
AT 1 T1
AT 2 T2
AT 3 T3
BT 1 B1现在想根据tb1的CODE取出tb2的TYPE_CODE,结果如下
TYPE_CODE
T1
T3就是把tb1的CODE分解开,A对应TYPE1的AT, 前面的数字对应TYPE2的数字
该怎么做呢
tb1CODE
1A
3Atb2
TYPE1 TYPE2 TYPE_CODE
AT 1 T1
AT 2 T2
AT 3 T3
BT 1 B1现在想根据tb1的CODE取出tb2的TYPE_CODE,结果如下
TYPE_CODE
T1
T3就是把tb1的CODE分解开,A对应TYPE1的AT, 前面的数字对应TYPE2的数字
该怎么做呢
where tb2.TYPE1 = left(tb1.CODE,1) and tb2.type2 = right(tb1.code,1)
insert into tb1
select '1A'
union all select '3A'create table tb2(type1 varchar(10),type2 int,type_code varchar(10))
insert into tb2
select 'AT',1,'T1'
union all select 'AT',2,'T2'
union all select 'AT',3,'T3'
union all select 'BT',1,'B1'select type_code from tb2,tb1
where charindex(tb1.code,tb2.type_code+tb2.type1)>0
create table tb1(CODE varchar(10))
create table tb2(TYPE1 varchar(10),TYPE2 varchar(10),TYPE_CODE varchar(10))--插入测试数据
insert tb1(CODE)
select '1A' union all
select '3A'insert tb2(TYPE1,TYPE2,TYPE_CODE)
select 'AT','1','T1' union all
select 'AT','2','T2' union all
select 'AT','3','T3' union all
select 'BT','1','B1'--求解过程
select type_code from tb2
where exists
(select 1 from tb1
where left(tb2.type1,1) = right(tb1.code,1)
and tb2.type2 = left(tb1.code,1) )--删除测试环境
drop table tb1,tb2/*--测试结果
type_code
----------
T1
T3
*/
drop table tb1
go
create table tb1(CODE varchar(10))
insert into tb1(CODE) values('1A')
insert into tb1(CODE) values('3A')if object_id('pubs..tb2') is not null
drop table tb2
go
create table tb2(TYPE1 varchar(10),TYPE2 varchar(10),TYPE_CODE varchar(10))
insert into tb2(TYPE1,TYPE2,TYPE_CODE) values('AT', '1', 'T1')
insert into tb2(TYPE1,TYPE2,TYPE_CODE) values('AT', '2', 'T2')
insert into tb2(TYPE1,TYPE2,TYPE_CODE) values('AT', '3', 'T3')
insert into tb2(TYPE1,TYPE2,TYPE_CODE) values('BT', '1', 'B1')
goselect tb2.TYPE_CODE from tb2,tb1
where left(tb2.TYPE1,1) = right(tb1.CODE,1) and tb2.type2 = left(tb1.code,1)drop table tb1,tb2/*
TYPE_CODE
----------
T1
T3(所影响的行数为 2 行)
*/