create table A(AID int, AName varchar(10))
insert into a values(1 , '苹果')
create table b(BID int, BName varchar(10) , AID int)
insert into b values(1 , '河南' , 1 )
insert into b values(2 , '山东' , 1 )
create table c(CID int, CName varchar(10) , AID int, BIDS varchar(10))
insert into c values(1 , '果汁' , 1 , '3,2')
insert into c values(2 , '果醋' , 1 , '1')
go
SELECT
A.CName,
B.AName,
C.BName
FROM (
SELECT
A.CID,
A.CName,
A.AID,
SUBSTRING(A.BIDS,B.number,CHARINDEX(',',A.BIDS+',',B.number)-B.number) AS BID
FROM C AS A
JOIN master..spt_values AS B
ON B.type='p' AND B.number >0
AND SUBSTRING(','+A.BIDS,B.number,1)=','
) AS A
JOIN A AS B
ON A.AID=B.AID
JOIN B AS C
ON A.BID=C.BID
drop table a , b , c