关联查询求助

[老七]

有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

求结果表关联查询语句

[开启时代]

加和 再 join 就可以了 。

[老七]

能否用一条语句实现

[我腫了]

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

[老七]

--USE test GO ---->生成表a -- -- if object_id('a') is not null drop table a 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 Create table b([id] smallint,[j2] smallint) Insert into b Select 1,10 Union all Select 1,20 Union all Select 1,30 Insert into b Select 2,10 Union all Select 2, 2 Union all Select 2,3 --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 a.id 显示顺序有点问题

[我腫了]

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
Union all Select 2,35
Union all Select 2,5
Union all Select 3,70

GO


SELECT
		*
	FROM (
			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),'')
				FROM a
					LEFT JOIN b ON a.id=b.id
	) AS t				
ORDER BY id,j1+'a',LEN(j2),j2

/*
id     j1     j2
------ ------ ------------
1      100    60
1             10
1             20
1             30
2      200    40
2             5
2             35
3      300    70
3             70
*/

[老七]

非常谢谢，结贴给分