ScoreA 表:
chrNumber intScoreA
002 70
003 75ScoreB表:
chrNumber intScoreB
001 90
002 85如何获得以下结果??
chrNumber ScoreA ScoreB
001 0 90
002 70 85
003 75 0
chrNumber intScoreA
002 70
003 75ScoreB表:
chrNumber intScoreB
001 90
002 85如何获得以下结果??
chrNumber ScoreA ScoreB
001 0 90
002 70 85
003 75 0
isnull(a.chrNumber,b.chrNumber) as chrNumber,
isnull(a.intScoreA,0) as ScoreA,
isnull(b.intScoreB,0) as ScoreB
from
ScoreA a
full outer join
ScoreB b
on
a.chrNumber=b.chrNumber
INSERT @TA
SELECT '002', 70 UNION ALL
SELECT '003', 75DECLARE @TB TABLE([chrNumber] VARCHAR(3), [intScoreB] INT)
INSERT @TB
SELECT '001', 90 UNION ALL
SELECT '002', 85SELECT [chrNumber],SUM(ScoreA) as ScoreA,SUM(ScoreB) as ScoreB
FROM (
SELECT [chrNumber],[intScoreA] AS ScoreA,0 AS [ScoreB] FROM @TA
UNION ALL
SELECT [chrNumber],0 AS [intScoreA],[intScoreB] FROM @TB
) T
GROUP BY [chrNumber]
/*
chrNumber ScoreA ScoreB
--------- ----------- -----------
001 0 90
002 70 85
003 75 0
*/
INSERT @TA
SELECT '002', 70 UNION ALL
SELECT '003', 75DECLARE @TB TABLE([chrNumber] VARCHAR(3), [intScoreB] INT)
INSERT @TB
SELECT '001', 90 UNION ALL
SELECT '002', 85SELECT ISNULL(A.[chrNumber],B.[chrNumber]) AS [chrNumber],
ISNULL([intScoreA],0) AS ScoreA,
ISNULL([intScoreB],0) AS ScoreB
FROM @TA AS A FULL JOIN @TB AS B ON
A.[chrNumber]=B.[chrNumber]
ORDER BY [chrNumber]
/*
chrNumber ScoreA ScoreB
--------- ----------- -----------
001 0 90
002 70 85
003 75 0
*/