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-->生成表aif 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-->生成表bif 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,70GO 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 */
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
显示顺序有点问题
GO-->生成表aif 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-->生成表bif 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,70GO
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
*/