查询问题----------应该也算简单吧
ta
id bh1 bh2 ----bh2是smallint类型
1 aaa 1
2 bbb 101

tb
id bh
1 aaa-001
2 bbb-101
3 ccc-002

现在我要
select * from ta,tb where bh1+bh2=tb.bh
由于ta中bh2是smallint类型，在tb 中bh为***-001时，bh2为1

请问怎么处理才可以查询出来呢？

------解决方案--------------------
select * from ta join tb on rtrim(ta.bh1)+'-'+rtrim(ta.bh2)=tb.bh
------解决方案--------------------
declare @T table(ID int,bh1 varchar(5),bh2 smallint)
insert into @T select 1,'aaa',1
union all select 2,'bbb',101

declare @B table(ID int,bh varchar(10))
insert into @B select 1,'aaa-001'
union all select 2,'bbb-101'

select * from @T a,@B b where a.bh1+'-'+right('000'+cast(a.bh2 as varchar(5)),3)=b.bh

试试看
------解决方案--------------------
create table #ta

(id int, bh1 varchar(10), bh2 int)
insert into #ta
select 1, 'aaa', 1 union all
select 2, 'bbb', 101 

create table #tb
(id int, bh varchar(10))
insert into #tb
select 1, 'aaa-001' union all 
select 2, 'bbb-101' union all 
select 3, 'ccc-002' 


select *
from #ta a ,#tb b
where 
a.bh1+'-'+ case when len(cast(a.bh2 as varchar(10)))=1 then '00'+cast(a.bh2 as varchar(10))
when len(cast(a.bh2 as varchar(10)))=2 then '0'+ cast(a.bh2 as varchar(10))
when len(cast(a.bh2 as varchar(10)))=3 then cast(a.bh2 as varchar(10))
end 
=b.bh
  

id bh1 bh2 id bh
----------- ---------- ----------- ----------- ---------- 
1 aaa 1 1 aaa-001
2 bbb 101 2 bbb-101

（所影响的行数为 2 行）
------解决方案--------------------
select *
from ta, tb
where bh1 = SubString(bh, 1, 3) and bh2 = Cast(SubStrin(bh, 5, 3) as smallint)
------解决方案--------------------

SQL code

declare @T table(ID int,bh1 varchar(5),bh2 smallint) 
insert into @T select 1, 'aaa ',1 
union all select 2, 'bbb ',101 

declare @B table(ID int,bh varchar(10)) 
insert into @B select 1, 'aaa-001 ' 
union all select 2, 'bbb-101 ' 
--把你字段的空格去掉
select rtrim(bh1)+'-'+right(rtrim('000'+cast(bh2 as varchar(20))),3) from @t


select a.* from @t a,@b b where rtrim(bh1)+'-'+right(rtrim('000'+cast(bh2 as varchar(20))),3)=b.bh

------解决方案--------------------
SQL code

[code=SQL]
declare @T table(ID int,bh1 varchar(5),bh2 smallint) 
insert into @T select 1, 'aaa ',1 
union all select 2, 'bbb ',101 

declare @B table(ID int,bh varchar(10)) 
insert into @B select 1, 'aaa-001 ' 
union all select 2, 'bbb-101 ' 

select a.* from @t a,@b b where rtrim(bh1)+'-'+right(rtrim('000'+cast(bh2 as varchar(20))),3)=b.bh

------解决方案--------------------
SQL code
--原始数据：@ta
declare @ta table(id int,bh1 varchar(3),bh2 smallint)
insert @ta
select 1,'aaa',1 union all
select 2,'bbb',101
--原始数据：@tb
declare @tb table(id int,bh varchar(7))
insert @tb
select 1,'aaa-001' union all
select 2,'bbb-101' union all
select 3,'ccc-002'

--1：@tb.bh两部分的长度都不固定
select * from @ta a,@tb b where a.bh1=left(b.bh,charindex('-',b.bh)-1) and a.bh2=right(b.bh,len(b.bh)-charindex('-',b.bh))

--2：@tb.bh的数字部分长度固定
select * from @ta a,@tb b where a.bh1+'-'+replicate('0',3-len(a.bh2))+ltrim(a.bh2)=b.bh

--3：@tb.bh两部分的长度固定
select * from @ta a,@tb b where a.bh1=left(b.bh,3) and a.bh2=right(b.bh,3)

/*
id          bh1  bh2    id          bh