A表
AID A_col1 A_col2 A_col3
1 200 a12 a13
2 200 a22 a23
3 200 a32 a33
4 201 a42 a43
5 201 a52 a53
6 201 a62 a63B表
BID B_col1 B_col2 B_col3
1 200 1,2 b13
2 200 3 b23
3 201 4,5,6 b33表的关系:
B_Col2 为AID的组合A_col1 中的值对与B_col1是有对应关系想取的结果为:
200 a12 a13 b13
201 a42 a43 b33
201 a53 a53 b33
(解释一下: 从A表选出一组数,通过B表的Col1,与Col2 的关系 把B表的Col3挂到A表后面 )
AID A_col1 A_col2 A_col3
1 200 a12 a13
2 200 a22 a23
3 200 a32 a33
4 201 a42 a43
5 201 a52 a53
6 201 a62 a63B表
BID B_col1 B_col2 B_col3
1 200 1,2 b13
2 200 3 b23
3 201 4,5,6 b33表的关系:
B_Col2 为AID的组合A_col1 中的值对与B_col1是有对应关系想取的结果为:
200 a12 a13 b13
201 a42 a43 b33
201 a53 a53 b33
(解释一下: 从A表选出一组数,通过B表的Col1,与Col2 的关系 把B表的Col3挂到A表后面 )
from a,b
where charindex(','+ltrim(a.aid)+',',','+B_col2+',')>0
FROM A JOIN B ON A.A_col1=B.B_col1
where CHARINDEX(RTRIM(a.AID),b.B_COL2)>0
if object_id('[A]') is not null drop table [A]
go
create table [A]([AID] int,[A_col1] int,[A_col2] varchar(3),[A_col3] varchar(3))
insert [A]
select 1,200,'a12','a13' union all
select 2,200,'a22','a23' union all
select 3,200,'a32','a33' union all
select 4,201,'a42','a43' union all
select 5,201,'a52','a53' union all
select 6,201,'a62','a63'
if object_id('[B]') is not null drop table [B]
go
create table [B]([BID] int,[B_col1] int,[B_col2] varchar(5),[B_col3] varchar(3))
insert [B]
select 1,200,'1,2','b13' union all
select 2,200,'3','b23' union all
select 3,201,'4,5,6','b33'
select A_col1,A_col2,A_col3,B_Col3
from a,b
where a.A_col1=b.B_col1
and charindex(','+ltrim(a.aid)+',',','+B_col2+',')>0/**
A_col1 A_col2 A_col3 B_Col3
----------- ------ ------ ------
200 a12 a13 b13
200 a22 a23 b13
200 a32 a33 b23
201 a42 a43 b33
201 a52 a53 b33
201 a62 a63 b33(6 行受影响)
**/