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

Question

做数据时有两张表 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       

Answer 1

select *
from t_B
where len(barcode)=12

Answer 2

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))
)

Answer 3

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

Answer 4

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)) 

Answer 5

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

Answer 6

---测试数据---
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 行）

Answer 7

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          

Answer 8

---测试数据---
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 行）

Answer 9

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