sos:求助，一条SQL语句（一个表的字段内容，另一个表的字段内容的一部分如何连接？）

[ittdmb]

例：有两个表。
A表
ID 名称
101 AAAA
102 BBBB
103 CCCC
104 DDDD

B表
ID AID组
601 101,102

我要写一条语句，显示A表中，未被B表的[AID组]字段所包含的内容，即
103 CCCC
104 DDDD
请问这个语句该怎么写？

[liangCK]

先拆表B，再连接。

[Limpire]

--> 测试数据: #A
if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A (ID int,名称 varchar(4))
insert into #A
select 101,'AAAA' union all
select 102,'BBBB' union all
select 103,'CCCC' union all
select 104,'DDDD'
--> 测试数据: #B
if object_id('tempdb.dbo.#B') is not null drop table #B
create table #B (ID int,AIDs varchar(100))
insert into #B
select 601,'101,102'

select * from #A t where not exists (select 1 from #B where charindex(','+ltrim(t.ID)+',', ','+AIDs+',')>0)
/*
ID          名称
----------- ----
103         CCCC
104         DDDD
*/

[liangCK]

create table tb1(id int,name varchar(10))
insert into tb1 select 101 ,   'AAAA' 
insert into tb1 select 102 ,   'BBBB' 
insert into tb1 select 103 ,   'CCCC' 
insert into tb1 select 104 ,   'DDDD'

create table tb2(id int,aid varchar(50))
insert into tb2 select 601,'101,102'

go

select top 50 id=identity(int,1,1) into # from syscolumns a

select a.id,b.name
from
(
   select a.id,aid=substring(a.aid,b.id,charindex(',',a.aid+',',b.id)-b.id)
   from tb2 a,# b
   where substring(','+a.aid,b.id,1)=','
) a,tb1 b
where a.aid=b.id

drop table tb1,tb2,#


/*
id          name       
----------- ---------- 
601         AAAA
601         BBBB

（所影响的行数为 2 行）
*/

[liangCK]

错了，

[liangCK]

看错，sorry.

[zccmy22]

declare @sql varchar(1000)
set @sql= 'select cast(a.id as varchar) from a , b where ''cast(b.AID组 as varchar) '' like cast(''%'' + cast(a.id as varchar) + ''%'' as varchar)'
exec(@sql)类似的就可以吧。

[zccmy22]

借用了一下楼上的建表。呵呵。

if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A (ID int,名称 varchar(4))
insert into #A
select 101,'AAAA' union all
select 102,'BBBB' union all
select 103,'CCCC' union all
select 104,'DDDD'
--> 测试数据: #B
if object_id('tempdb.dbo.#B') is not null drop table #B
create table #B (ID int,AIDs varchar(100))
insert into #B
select 601,'101,102'

declare @sql varchar(1000)
set @sql= 'select cast(a.id as varchar) from #A a,#B b where cast(b.AIDs as varchar) like cast(''%'' + cast(a.id as varchar) + ''%'' as varchar)'
select @sql
exec(@sql)

[hz_80]

select A.* from A , B where not (B.AID组 like '%' + A.ID + '%')
对不?

[青锋-SS]

[Quote=引用楼主 ittdmb 的帖子:]
A表
ID 名称
101 AAAA
102 BBBB
103 CCCC
104 DDDD
B表
ID AID组
601 101,102
我要写一条语句，显示A表中，未被B表的[AID组]字段所包含的内容，即
103 CCCC
104 DDDD
请问这个语句该怎么写？
[/Quote]select * from a where exists(select * from b where charindex(','+a.id+',',','+aid+',')>0)

[ittdmb]

ddd

[wujumao]

学习了