请教个ＳＱＬ拆分查询语句？？？？？

Question

表A
AID   AName
1      苹果
表B（A表的产地）
BID  BName   AID
1     河南    1
2     山东    1
表C（使用情况）
CID  CName  AID  BIDS
1     果汁   1    1,2
2     果醋   1     1得到结果
CName   AName   BName
果汁     苹果     河南
果汁     苹果     山东
果醋     苹果     河南

Answer 1

对c表先按如下的方法拆分,然后再关联./*
标题：分拆列值
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-11-20
地点：广东深圳
描述有表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)
)BDROP TABLE tb/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc(5 行受影响)
*/

Answer 2

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

Answer 3

--SQL 2000create 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 , '1,2') 
insert into c values(2 , '果醋' , 1 , '1')
go--临时表
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b select t1.cname , t2.aname , t3.bname from
(
SELECT A.CID, A.CName , a.aid , BIDS = SUBSTRING(A.[BIDS], B.id, CHARINDEX(',', A.[BIDS] + ',', B.id) - B.id)
FROM c A, # B
WHERE SUBSTRING(',' + A.[BIDS], B.id, 1) = ','
) t1 , a t2 , b t3
where t1.aid = t2.aid and t1.bids = t3.bidDROP TABLE #
drop table a , b , c /*
cname      aname      bname      
---------- ---------- ---------- 
果汁         苹果         河南
果汁         苹果         山东
果醋         苹果         河南（所影响的行数为 3 行）
*/

Answer 4

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: @表A
DECLARE @表A TABLE (AID INT,AName VARCHAR(4))
INSERT INTO @表A
SELECT 1,'苹果'
 
--> 生成测试数据: @表B
DECLARE @表B TABLE (BID INT,BName VARCHAR(4),AID INT)
INSERT INTO @表B
SELECT 1,'河南',1 UNION ALL
SELECT 2,'山东',1
 
--> 生成测试数据: @表C
DECLARE @表C TABLE (CID INT,CName VARCHAR(4),AID INT,BIDS VARCHAR(20))
INSERT INTO @表C
SELECT 1,'果汁',1,'1,2' UNION ALL
SELECT 2,'果醋',1,'1'--SQL查询如下: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 AND B.AID=C.AID/*
CName AName BName
----- ----- -----
果汁    苹果    河南
果汁    苹果    山东
果醋    苹果    河南(3 行受影响)*/

Answer 5

--SQL 2005
create table A(AID int, AName nvarchar(10))
insert into a values(1 , N'苹果') 
create table b(BID int, BName nvarchar(10) , AID int)
insert into b values(1 , N'河南' , 1 )
insert into b values(2 , N'山东' , 1 )
create table c(CID int, CName nvarchar(10) , AID int, BIDS varchar(10))
insert into c values(1 , N'果汁' , 1 , '1,2') 
insert into c values(2 , N'果醋' , 1 , '1')
goselect t1.cname , t2.aname , t3.bname from
(
SELECT e.CID, e.CName , e.aid , f.BIDS
FROM(
    SELECT CID, cname , aid , [BIDS] = CONVERT(xml,'<root><v>' + REPLACE([BIDS], ',', '</v><v>') + '</v></root>') FROM c
)e
OUTER APPLY(
    SELECT BIDS = N.v.value('.', 'nvarchar(100)') FROM e.[BIDS].nodes('/root/v') N(v)
)f
) t1 , a t2 , b t3
where t1.aid = t2.aid and t1.bids = t3.biddrop table A , B, C/*
cname      aname      bname
---------- ---------- ----------
果汁         苹果         河南
果汁         苹果         山东
果醋         苹果         河南(3 行受影响)*/

Answer 6

DECLARE @A TABLE
(
AID INT,
AName VARCHAR(100)
)
INSERT @A SELECT 1, '苹果'DECLARE @B TABLE 
(
BID INT,
BName VARCHAR(100),
AID INT 
)
INSERT @B SELECT 1, '河南', 1
INSERT @B SELECT 2, '山东', 1DECLARE @C TABLE
(
CID INT,
CName VARCHAR(100),
AID INT,
BIDS VARCHAR(100)
)
INSERT @C SELECT 1, '果汁', 1, '1,2'
INSERT @C SELECT 1, '果醋', 1, '1'
--方法一
SELECT CName, AName, BName FROM @C t1 JOIN @A t2 ON  t1.AID = t2.AID
OUTER APPLY 
(SELECT * FROM @B t3 
  WHERE 1=0 OR
  PATINDEX ('%,' + CONVERT(VARCHAR(10),t3.BID) + ',%' , t1.bids)>0 
     OR PATINDEX (CONVERT(VARCHAR(10),t3.BID) + ',%' , t1.bids)>0 
   OR PATINDEX ('%,' + CONVERT(VARCHAR(10),t3.BID) , t1.bids)>0 
    OR CONVERT(VARCHAR(10),t3.BID) = t1.bids 
  ) AS tt
  
  
 --方法二
  SELECT CName, AName, BName FROM @C t1 JOIN @A t2 ON  t1.AID = t2.AID
JOIN @B t3 
  ON
  PATINDEX ('%,' + CONVERT(VARCHAR(10),t3.BID) + ',%' , t1.bids)>0 
     OR PATINDEX (CONVERT(VARCHAR(10),t3.BID) + ',%' , t1.bids)>0 
   OR PATINDEX ('%,' + CONVERT(VARCHAR(10),t3.BID) , t1.bids)>0 
    OR CONVERT(VARCHAR(10),t3.BID) = t1.bids