看结果没看出楼主想做什么 如果是:选出A表中的Col必须存在于B表中的记录 select * from A表 where col in (select col from B表)
select * from a where exists(select * from b where a.col=b.col and charindex(a.value,b.[text])>0)
--还是这个意思?select * from a where not exists( select * from b where col1=a.col1 and charindex(a.value,b.[text])=0)
select dd.col,dd.value from ( select distinct a.*,个数=count(b.[text]) from a,b where a.col=b.col and charindex(a.value,b.[text])>0 group by a.col,a.value )dd inner join (select b.col,总数=count(*) from b group by b.col)ee on dd.col=ee.col where dd.个数=ee.总数
create table a(col int,value char(5)) create table b(col int,text char(5)) insert into b select '1','qwe' union all select '1','qqw' union all select '1','qwr' union all select '2','ewr' union all select '2','we' union all select '2','wet' insert into a select '1','q' union all select '1','e' union all select '1','t' union all select '2','e' union all select '2','f' union all select '2','g' select * from a select * from b 你要的肯丁是這個結果 select distinct * from a where exists (select * from b where col = a.col and a.value = left(b.text,1)) =--------- 1 q 2 e
如果是:选出A表中的Col必须存在于B表中的记录
select * from A表
where col in (select col from B表)
where exists(select * from b where a.col=b.col and charindex(a.value,b.[text])>0)
where not exists(
select * from b where col1=a.col1 and charindex(a.value,b.[text])=0)
(
select distinct a.*,个数=count(b.[text]) from a,b where a.col=b.col and charindex(a.value,b.[text])>0
group by a.col,a.value
)dd inner join (select b.col,总数=count(*) from b group by b.col)ee
on dd.col=ee.col
where dd.个数=ee.总数
create table b(col int,text char(5))
insert into b
select '1','qwe'
union all
select '1','qqw'
union all
select '1','qwr'
union all
select '2','ewr'
union all
select '2','we'
union all
select '2','wet'
insert into a
select '1','q'
union all
select '1','e'
union all
select '1','t'
union all
select '2','e'
union all
select '2','f'
union all
select '2','g'
select * from a
select * from b
你要的肯丁是這個結果
select distinct * from a
where exists
(select * from b where col = a.col and a.value = left(b.text,1))
=---------
1 q
2 e
其实是要:
相同col值,在a表中每条记录的value值,在b表中的[text]中的记录中均要出现。
楼上的只是对应第一个字符了,如果改一下数据就看出来了。老大的最好!建议使用。
我的也可以用,如果不嫌麻烦 :P