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 @tselect 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
--原始数据：@tadeclare @ta table(id int,bh1 varchar(3),bh2 smallint)insert @taselect 1,'aaa',1 union allselect 2,'bbb',101--原始数据：@tbdeclare @tb table(id int,bh varchar(7))insert @tbselect 1,'aaa-001' union allselect 2,'bbb-101' union allselect 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 ----------- ---- ------ ----------- ------- 1 aaa 1 1 aaa-0012 bbb 101 2 bbb-101*/