CREATE TABLE #C(id VARCHAR(20),name VARCHAR(200))
INSERT INTO #C
SELECT 'c1','A001' UNION ALL
SELECT 'c2','A002' UNION ALL
SELECT 'c3','A003' UNION ALL
SELECT 'c4','A004'
CREATE TABLE #B(bid VARCHAR(20),cid VARCHAR(20))
INSERT INTO #B
SELECT 'b1','c1' UNION ALL
SELECT 'b1','c2' UNION ALL
SELECT 'b1','c3' UNION ALL
SELECT 'b2','c1' UNION ALL
SELECT 'b2','c4' UNION ALL
SELECT 'b3','c2' UNION ALL
SELECT 'b3','c3' UNION ALL
SELECT 'b3','c4' UNION ALL
SELECT 'b4','c2'
CREATE TABLE #A(id int,aname VARCHAR(20),aic VARCHAR(50))
INSERT INTO #A
SELECT 1,'AAA','b1' UNION ALL
SELECT 2,'BBB','b2' UNION ALL
SELECT 3,'CCC','b3' UNION ALL
SELECT 4,'DDD','b4' UNION ALL
SELECT 5,'EEE','b1'
#A.aic = #B.bid
#B.cid = #C.id
輸出如下結果：
id aname acname
1 AAA A001,A002,A003
2 BBB A001,A004
3 CCC A002,A003
4 DDD A002
5 EEE A001,A002,A003
uj5u.com熱心網友回復：
SELECT A.ANAME,STUFF((SELECT ','+NAME FROM #B B JOIN #C C ON B.cid=C.id WHERE B.bid=A.AIC FOR XML PATH('')),1,1,'')
FROM #A A
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/227432.html
標籤：基礎類