关联查询求助
有a,b两表
a表：
id j1
1 100
2 200
3 300

b表：
id j2
1 10
1 20
1 30

结果表：
id j1 j2
1 100 60
1 10
1 20
1 30
2
3

求结果表关联查询语句
[解决办法]

USE test
GO



---->生成表a
--
--if object_id('a') is not null 
--drop table a
--Go
--Create table a([id] smallint,[j1] smallint)
--Insert into a
--Select 1,100
--Union all Select 2,200
--Union all Select 3,300
--
---->生成表b
--
--if object_id('b') is not null 
--drop table b
--Go
--Create table b([id] smallint,[j2] smallint)
--Insert into b
--Select 1,10
--Union all Select 1,20
--Union all Select 1,30
--
--GO


/*
结果表：
id j1   j2
1  100  60
1       10
1       20
1       30
2
3
*/

SELECT 
a.id
,LTRIM(a.j1)AS j1
,LTRIM(SUM(b.j2))AS j2 
FROM a 
INNER JOIN b ON a.id=b.id
GROUP BY a.id,a.j1
UNION ALL
SELECT 
a.id
,''
,ISNULL(LTRIM(b.j2),'') AS j2 
FROM a
LEFT JOIN b ON a.id=b.id
ORDER BY id