sql 解释带分割符的字符串

 

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