两表查询问题
公司表T_company
ID Name
1 a
2 b
3 c

产品表T_Product
PID company
8 1,2
9 3
10 1,2,3


T_Product里面的company   里面存储的是T_company里面的ID组合
希望得到的查询结果
PID ID Name
8 1 a
8 2 b
9 3 C
10 1 a
10 2 b
10 3 C




------解决方案--------------------

create table T_company(ID int, Name varchar(10))
insert T_company select 1, 'a '
union all select 2, 'b '
union all select 3, 'c '

create table T_Product(PID int, company varchar(20))
insert T_Product select 8, '1,2 '
union all select 9, '3 '
union all select 10, '1,2,3 '

select top 8000 ID=identity(int, 1, 1) into #T from syscolumns, sysobjects

select * from
(
select PID, ID=cast(substring(A.company, B.ID, charindex( ', ', A.company+ ', ', B.ID)-B.ID) as varchar(10))
from T_Product A, #T B
where B.ID <=len(A.company)
and charindex( ', ', ', '+A.company, B.ID)=B.ID
)tmp
left join T_company
on tmp.ID=T_company.ID

--result
PID ID ID Name
----------- ---------- ----------- ----------
8 1 1 a
8 2 2 b
9 3 3 c
10 1 1 a
10 2 2 b
10 3 3 c

(6 row(s) affected)
------解决方案--------------------
公司表T_company
ID Name
1 a
2 b
3 c

产品表T_Product
PID company
8 1,2
9 3
10 1,2,3

select a.pid,b.id,b.name
from T_Product a,T_company
where charindex(b.id,a.company)> 0
order by a.pid