如:Tbl_A
PrjId PrjName ...
1 a
2 b
3 c
4 d
5 e
... ...Tbl_B
PrjId PrjIntro ...
1 abc
3 acde
4 cdfe
... ...对B表需要查找所有PrjIntro字段中包含'a'字母的PrjId集合:
SELECT PrjId FROM Tbl_B WHERE CHARINDEX('a',PrjIntro) > 0然后将查询结果(一个或者多个PrjId)作为A表的查询条件:
SELECT PrjId,PrjName FROM Tbl_A WHERE PrjId = SOME(SELECT PrjId FROM Tbl_B WHERE CHARINDEX('a',PrjIntro) > 0)把SOME换成 IN 或者 = 也都差不出来结果
需要怎样修改呢?
PrjId PrjName ...
1 a
2 b
3 c
4 d
5 e
... ...Tbl_B
PrjId PrjIntro ...
1 abc
3 acde
4 cdfe
... ...对B表需要查找所有PrjIntro字段中包含'a'字母的PrjId集合:
SELECT PrjId FROM Tbl_B WHERE CHARINDEX('a',PrjIntro) > 0然后将查询结果(一个或者多个PrjId)作为A表的查询条件:
SELECT PrjId,PrjName FROM Tbl_A WHERE PrjId = SOME(SELECT PrjId FROM Tbl_B WHERE CHARINDEX('a',PrjIntro) > 0)把SOME换成 IN 或者 = 也都差不出来结果
需要怎样修改呢?
FROM Tbl_A
WHERE PrjId IN (SELECT distinct PrjId FROM Tbl_B WHERE PrjIntro like '%a%')
WHERE PrjId in (
SELECT PrjId FROM Tbl_B
WHERE CHARINDEX('a',PrjIntro) > 0
)
FROM Tbl_A a, Tbl_B b
WHERE a.PrjId = b.PrjId and CHARINDEX('a',bPrjIntro) > 0)
FROM Tbl_A a, Tbl_B b
WHERE a.PrjId = b.PrjId and CHARINDEX('a',b.PrjIntro) > 0)
insert Tbl_A select 1, 'a'
union all select 2, 'b'
union all select 3, 'c'
union all select 4, 'd'
union all select 5, 'e'create table Tbl_B(PrjId int, PrjIntro varchar(20))
insert Tbl_B select 1, 'abc'
union all select 3, 'acde'
union all select 4, 'cdfe'select Tbl_A.* from Tbl_A
inner join
(
select PrjId from Tbl_B where charindex('a', PrjIntro)>0
)Tbl_B on Tbl_A.PrjId=Tbl_B.PrjId--result
PrjId PrjName
----------- ----------
1 a
3 c(2 row(s) affected)