SQL 表连结问题请教

Question

现今遇到如下问题，忘有高人能给予帮助，谢谢。
TABLE A 表 中有ID字段 AID 和其他若干...
TABLE B 表 中有ID字段BID 和 A表的主键 AID列，但是B表中一条B记录可能对应两条A表中的两条A记录，所以俺的前辈们（俺是修改别人程序）就把数据设计成这样，B表中的AID列存储了所有与之对应的A表的AID，存储方式如下:
 TABLE A :            TABLE B:
  AID ...                BID  AID    ...
   1  ...                 1   1,2    ...
   2  ...现在问题是如果才能把A和B连结起来，而且不会出现 A和B 相同的记录,我尝试过 LEFT JOIN ON A.AID LIKE B.AID ,效率根本没法用，请高人帮忙解决，谢谢...

Answer 1

select a.* , b.*
from a , b
where charindex(','+a.aid + ',' , ','+b.bid+',') > 0

Answer 2

啊哦提交后把样式打乱了，不好意思。
表结构是这样的
TABLE A:
AID ANAME ASCHOOL
 1    XX     XX
 2    XX     XX
 3    XX     XXTABLE B :
BID BNAME BSCHOOL AID
 1    XX     XX   1,2

Answer 3

这种表结构效率肯定不会高的，我可能会有这样的查询
select a.*,b.* from a,b where a.id in (b.aid)

Answer 4

谢谢您的帮助，但现在看来好像效果不佳，俺说一下，俺的A表大概有70W条记录，B表有7W条记录，而且B表这7W条数据大部分都是从A表中复制或者合并过来的...目前正在执行你的SQL，已用时间4分30秒...还没结束...

Answer 5

就是分割串拆分字符串实例--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,name varchar(8))
insert into #T
select 1,'jame,job' union all
select 2,'paul,mc' union all
select 3,'carl';--> 1. CTE 递归找分隔字符位置法：速度极快
with T (id,P1,P2) as
(
    select id,charindex(',',','+name),charindex(',',name+',')+1 from #T
    union all
    select a.id,b.P2,charindex(',',name+',',b.P2)+1 from #T a join T b on a.id=b.id where charindex(',',name+',',b.P2)>0
)
select a.id,name=substring(a.name+',',b.P1,b.P2 - b.P1 - 1) from #T a join T b on a.id=b.id order by 1
/*
id          name
----------- ---------
1           jame
1           job
2           mc
2           paul
3           carl
*/
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,name varchar(8))
insert into #T
select 1,'jame,job' union all
select 2,'paul,mc' union all
select 3,'carl';--> 2. 临时表法：速度比CTE方法相差无几
if object_id('tempdb.dbo.#') is not null drop table #
select top 8000 id=identity(int,1,1) into # from syscolumns,sysobjects --> select max(len(name)) from #T=11
select a.id,name=substring(a.name+',',b.id,charindex(',',a.name+',',b.id+1)-b.id) from #T a inner join # b on substring(','+a.name,b.id,1)=','
/*
id          name
----------- ---------
1           jame
1           job
2           mc
2           paul
3           carl
*/3. XML法：速度较慢
select
    a.id,b.name
from
    (select id,name=convert(xml,'<root><v>'+replace(name,',','</v><v>')+'</v></root>') from #T ) a
outer apply
    (select name=N.v.value('.','varchar(100)') from a.name.nodes('/root/v') N(v)) b
/*
id          name
----------- ---------
1           jame
1           job
2           mc
2           paul
3           carl
*/
分拆列值 原著：邹建 
改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  2007-12-16  广东深圳 有表tb, 如下: 
id          value 
----------- ----------- 
1          aa,bb 
2          aaa,bbb,ccc 
欲按id,分拆value列, 分拆后结果如下: 
id          value 
----------- -------- 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc 1. 旧的解决方法(sql server 2000) 
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id) 
FROM tb A, # B 
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ',' DROP TABLE # 2. 新的解决方法(sql server 2005) create table tb(id int,value varchar(30)) 
insert into tb values(1,'aa,bb') 
insert into tb values(2,'aaa,bbb,ccc') 
go 
SELECT A.id, B.value 
FROM( 
    SELECT id, [value] = CONVERT(xml,' <root> <v>' + REPLACE([value], ',', ' </v> <v>') + ' </v> </root>') FROM tb 
)A 
OUTER APPLY( 
    SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v) 
)B DROP TABLE tb /* 
id          value 
----------- ------------------------------ 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc (5 行受影响) 
*/

Answer 6

有点多and有点深奥，正在研究中...