sql查询 三字段连接查询指定值

[imelodious]

做数据时有两张表 t_A(color varchar(20),size varchar(20),itemno varchar(20)),t_B(color varchar(20),size varchar(20),itemno varchar(20),barcode varchar(13)), t_A表中是业务员提供的数据,t_B中是所有的数据,现在问题是:barcode字段有12位和7位两种,例如：
t_A: color size itemno t_B color size itemno barcode
aaa 34 xU79910 aaa 34 xU79910 123456789012
bbb U LCas910 aaa 34 xU79910 1234567
ccc 54 bb9910 bbb U LCas910 34567890
ddd 23 er79910 ccc 54 bb9910 098765432123
ddd 23 er79910 012345678901
ddd 23 er79910 0123456

怎样从t_B中取出t_A中所对应的barcode值(连接条件:color,size,itmeno) 如果对应的barcode有12位和7位的，则取12位的
要求结果:
color size itemno barcode
aaa 34 xU79910 123456789012
bbb U LCas910 34567890
ccc 54 bb9910 098765432123
ddd 23 er79910 012345678901

[imelodious]

我想最后实现的结果是： 如果有12位和7位的数据，则取12位的,如果对应的数据只有一个，则取该数据即可

[imelodious]

第二条数据对应的barcode没有取出来,应该是对应的7位barcode:34567890

[wuyi8808]

select *
from t_B
where len(barcode)=12

[chenyanm]

select a.color,a.size,a.itemno,b.barcode from t_A a,t_B b
where a.color=b.color and a.size=b.size and a.itemno=b.itemno
and not exists(select 1 from t_B where color=b.color and size=b.size and itemno=b.itemno and len(barcode)>len(b.barcode))
)

[百年树人]

select b.color,b.size,b.itemno,isnull(a.barcode,b.itemno)
from t_B b
left join t_A a on a.color=b.color and a.size=b.size and a.itemno=b.itemno

[chenyanm]

select a.color,a.size,a.itemno,b.barcode from t_A a,t_B b
where a.color=b.color and a.size=b.size and a.itemno=b.itemno
and not exists(select 1 from t_B where color=b.color and size=b.size and itemno=b.itemno
and len(barcode)>len(b.barcode))

[demi2007]

select a.color,a.size,a.itemno,max(length(b.barcode)) as barcode
from t_a a inner join t_b b
on a.color=b.color and a.size=b.size and a.itemno=b.itemno
group by a.color,a.size,a.itemno

[百年树人]

---测试数据---
if object_id('[t_A]') is not null drop table [t_A]
go
create table [t_A]([color] varchar(3),[size] varchar(2),[itemno] varchar(7))
insert [t_A]
select 'aaa','34','xU79910' union all
select 'bbb','U','LCas910' union all
select 'ccc','54','bb9910' union all
select 'ddd','23','er79910'
if object_id('[t_B]') is not null drop table [t_B]
go
create table [t_B]([color] varchar(3),[size] varchar(2),[itemno] varchar(7),[barcode] varchar(12))
insert [t_B]
select 'aaa','34','xU79910','123456789012' union all
select 'aaa','34','xU79910','1234567' union all
select 'bbb','U','LCas910','34567890' union all
select 'ccc','54','bb9910','098765432123' union all
select 'ddd','23','er79910','012345678901' union all
select 'ddd','23','er79910','0123456'
 
---查询---
select a.color,a.size,a.itemno,isnull(b.barcode,a.itemno) as barcode
from t_A a
left join 
(select color,size,itemno,barcode
from t_B where len(barcode)=12) b
on a.color=b.color and a.size=b.size and a.itemno=b.itemno

---结果---
color size itemno  barcode      
----- ---- ------- ------------ 
aaa   34   xU79910 123456789012
bbb   U    LCas910 LCas910
ccc   54   bb9910  098765432123
ddd   23   er79910 012345678901

（所影响的行数为 4 行）

[bennyyyyy]

create table #tb1
(
color varchar(20),
size varchar(20),
itemno varchar(20)
)
create table #tb2
(
color varchar(20),
size varchar(20),
itemno varchar(20),
barcode varchar(13)
)
insert into #tb1
select 'aaa', '34' , 'xU79910' union all
select 'bbb', 'U', 'LCas910' union all
select 'ccc', '54', 'bb9910' union all
select 'ddd', '23', 'er79910'

insert into #tb2
select 'aaa', '34', 'xU79910', '123456789012' union all
select 'aaa', '34', 'xU79910', '1234567' union all
select 'bbb', 'U', 'LCas910', '34567890' union all
select 'ccc', '54', 'bb9910', '098765432123' union all
select 'ddd', '23', 'er79910', '012345678901' union all
select 'ddd', '23', 'er79910', '0123456'
select D.color,D.itemno,D.size,D.barcode from #tb2 D,
(select A.color,A.size,A.itemno,max(len(barcode)) leng from #tb1 A,#tb2 B where A.color=B.color and A.itemno=B.itemno and A.size=B.size group by A.color,A.itemno,A.size) C
where LEN(D.barcode)=C.leng and D.color=C.color and D.itemno=C.itemno and D.size=C.size

[lbcleo]

---测试数据---
if object_id('[t_A]') is not null drop table [t_A]
go
create table [t_A]([color] varchar(3),[size] varchar(2),[itemno] varchar(7))
insert [t_A]
select 'aaa','34','xU79910' union all
select 'bbb','U','LCas910' union all
select 'ccc','54','bb9910' union all
select 'ddd','23','er79910'
if object_id('[t_B]') is not null drop table [t_B]
go
create table [t_B]([color] varchar(3),[size] varchar(2),[itemno] varchar(7),[barcode] varchar(12))
insert [t_B]
select 'aaa','34','xU79910','123456789012' union all
select 'aaa','34','xU79910','1234567' union all
select 'bbb','U','LCas910','34567890' union all
select 'ccc','54','bb9910','098765432123' union all
select 'ddd','23','er79910','012345678901' union all
select 'ddd','23','er79910','0123456'

---查询---
select a.color,a.size,a.itemno,isnull(b.barcode,a.itemno) as barcode
from t_A a
left join
(select color,size,itemno,barcode
from t_B where len(barcode)=12) b
on a.color=b.color and a.size=b.size and a.itemno=b.itemno

---结果---
color size itemno barcode
----- ---- ------- ------------
aaa 34 xU79910 123456789012
bbb U LCas910 LCas910
ccc 54 bb9910 098765432123
ddd 23 er79910 012345678901

（所影响的行数为 4 行）