关联查询求助
有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
