表A:
ID(int) item1(varchar(50)) item2(varchar(50))
101 abc cba
102 aab acc
103 cb2a rjl
....................................
1101 dfaa dabea
1102 iefa lida
表B
header content [Varchar(50)]
first 1101/1203/
second 402/1302/ 现在要查询header 为first的对应记录
select * from A where CHARINDEX( cast(ID) as varchar(10),(select content from B where header='first')) 查询的结果是101ID对应的记录也查询出来了,请问这个问题怎么解决 谢谢
ID(int) item1(varchar(50)) item2(varchar(50))
101 abc cba
102 aab acc
103 cb2a rjl
....................................
1101 dfaa dabea
1102 iefa lida
表B
header content [Varchar(50)]
first 1101/1203/
second 402/1302/ 现在要查询header 为first的对应记录
select * from A where CHARINDEX( cast(ID) as varchar(10),(select content from B where header='first')) 查询的结果是101ID对应的记录也查询出来了,请问这个问题怎么解决 谢谢
from a,b
where charindex(rtrim(ID), content)>0 and header='first'
ID为int 类型 content为varchar(50)类型.
ID为int 类型 content为varchar(50)类型.
ID为int 类型 content为varchar(50)类型.
from a,b
where charindex('/'+rtrim(ID)+'/', '/'+content)>0 and header='first'
--> (让你望见影子的墙)生成测试数据,时间:2008-12-13
if not object_id('tb') is null
drop table tb
Go
Create table tb([ID] int,[item1] nvarchar(4),[item2] nvarchar(5))
Insert tb
select 101,N'abc',N'cba' union all
select 102,N'aab',N'acc' union all
select 103,N'cb2a',N'rjl' union all
select 1101,N'dfaa',N'dabea' union all
select 1102,N'iefa',N'lida'
Go
Select * from tb
--> (让你望见影子的墙)生成测试数据,时间:2008-12-13
if not object_id('tb2') is null
drop table tb2
Go
Create table tb2([header] nvarchar(6),[content] nvarchar(10))
Insert tb2
select N'first',N'1101/1203/' union all
select N'second',N'402/1302/'
Go
Select * from tb2
select * from tb where CHARINDEX( '/'+cast(ID as varchar(10))+'/','/'+(select content from tb2 where header='first')) >01101 dfaa dabea
drop table tb1
Go
Create table tb1([ID] int,[item1] nvarchar(4),[item2] nvarchar(5))
Insert tb1
select 101,N'abc',N'cba' union all
select 102,N'aab',N'acc' union all
select 103,N'cb2a',N'rjl' union all
select 1101,N'dfaa',N'dabea' union all
select 1102,N'iefa',N'lida'
Go
Select * from tb1
if not object_id('tb2') is null
drop table tb2
Go
Create table tb2([header] nvarchar(6),[content] nvarchar(10))
Insert tb2
select N'first',N'1101/1203/' union all
select N'second',N'402/1302/'
Go
Select * from tb2select a.* from tb1 a,tb2 b
where ID=left('1101/1203/',charindex('/','1101/1203/')-1) and header='first'
where ID=left((select content from tb2 where header='first')
,charindex('/',(select content from tb2 where header='first'))-1)
from a,b
where charindex('/'+rtrim(ID)+'/', '/'+content)>0 and header='first'这种方式漏掉了 ID为三位数的记录.select * from A where CHARINDEX( left('0'+cast(ID as varchar(10),4),(select content from B where header='first'))
这种方式 漏掉了ID为4位(即不要补充0的)记录
select a.*
from a,b
where charindex('/'+rtrim(ID)+'/', '/'+content+'/')>0 and header='first' 就可以了