CREATE TABLE #tempA ( AID int, ID1 int, ID2 int, ID3 int ) INSERT INTO #tempA(AID,ID1,ID2,ID3)VALUES(1,1,2,3) INSERT INTO #tempA(AID,ID1,ID2,ID3)VALUES(2,4,5,6) INSERT INTO #tempA(AID,ID1,ID2,ID3)VALUES(3,7,8,9)CREATE TABLE #tempB ( BID int, CValue int, Mark int ) INSERT INTO #tempB(BID,Cvalue,Mark)VALUES(1,11,0) INSERT INTO #tempB(BID,Cvalue,Mark)VALUES(2,12,0) INSERT INTO #tempB(BID,Cvalue,Mark)VALUES(3,13,1) INSERT INTO #tempB(BID,Cvalue,Mark)VALUES(4,14,0) INSERT INTO #tempB(BID,Cvalue,Mark)VALUES(5,15,1) INSERT INTO #tempB(BID,Cvalue,Mark)VALUES(6,16,1) INSERT INTO #tempB(BID,Cvalue,Mark)VALUES(7,17,0) INSERT INTO #tempB(BID,Cvalue,Mark)VALUES(8,18,1) INSERT INTO #tempB(BID,Cvalue,Mark)VALUES(9,19,0) SELECT AID, ID1, sum(CASE WHEN BID=id1 THEN Cvalue END) AS Cvalue1, sum(CASE WHEN BID=id1 THEN Mark END) AS Mark1, ID2, sum(CASE WHEN BID=id2 THEN Cvalue END) AS Cvalue2, sum(CASE WHEN BID=id2 THEN Mark END) AS Mark2, ID3, sum(CASE WHEN BID=id3 THEN Cvalue END) AS Cvalue3, sum(CASE WHEN BID=id3 THEN Mark END) AS Mark3 FROM #tempA AS a LEFT JOIN #tempB AS b ON ID1=BID OR ID2=BID OR ID3=BID GROUP BY AID,ID1,ID2,ID3
(
AID int,
ID1 int,
ID2 int,
ID3 int
)
INSERT INTO #tempA(AID,ID1,ID2,ID3)VALUES(1,1,2,3)
INSERT INTO #tempA(AID,ID1,ID2,ID3)VALUES(2,4,5,6)
INSERT INTO #tempA(AID,ID1,ID2,ID3)VALUES(3,7,8,9)CREATE TABLE #tempB
(
BID int,
CValue int,
Mark int
)
INSERT INTO #tempB(BID,Cvalue,Mark)VALUES(1,11,0)
INSERT INTO #tempB(BID,Cvalue,Mark)VALUES(2,12,0)
INSERT INTO #tempB(BID,Cvalue,Mark)VALUES(3,13,1)
INSERT INTO #tempB(BID,Cvalue,Mark)VALUES(4,14,0)
INSERT INTO #tempB(BID,Cvalue,Mark)VALUES(5,15,1)
INSERT INTO #tempB(BID,Cvalue,Mark)VALUES(6,16,1)
INSERT INTO #tempB(BID,Cvalue,Mark)VALUES(7,17,0)
INSERT INTO #tempB(BID,Cvalue,Mark)VALUES(8,18,1)
INSERT INTO #tempB(BID,Cvalue,Mark)VALUES(9,19,0)
SELECT AID,
ID1,
sum(CASE WHEN BID=id1 THEN Cvalue END) AS Cvalue1,
sum(CASE WHEN BID=id1 THEN Mark END) AS Mark1,
ID2,
sum(CASE WHEN BID=id2 THEN Cvalue END) AS Cvalue2,
sum(CASE WHEN BID=id2 THEN Mark END) AS Mark2,
ID3,
sum(CASE WHEN BID=id3 THEN Cvalue END) AS Cvalue3,
sum(CASE WHEN BID=id3 THEN Mark END) AS Mark3
FROM #tempA AS a LEFT JOIN #tempB AS b ON ID1=BID OR ID2=BID OR ID3=BID
GROUP BY AID,ID1,ID2,ID3
也感谢chenguang79,不过不太符合要求。TB表的字段如果很多的话,语句会很长。
再次感谢各位!