declare @a table(a varchar(6),b varchar(6),c varchar(10) ,d varchar(10))
insert @a select '0010', 'A001', 10 ,'20070430'
union all select '0020' ,'A002', 20 ,'20070430'declare @b table(a varchar(6),b varchar(6),c varchar(10),d varchar(10),e varchar(10))
insert @b select '0010', 'A001', 'a1', 5 ,'20070430'
union all select '0010', 'A001', 'b1', 2 ,'20070430'
union all select '0010', 'A001', 'c1', 3 ,'20070430'
union all select '0020', 'A002', 'a2', 15 ,'20070430'
union all select '0020', 'A002', 'b2', 5 ,'20070430'select a,b,c,d from(
select *,e=a from @a
union all
select b,d,c,e,a from @b )
dd
order by e,a
insert @a select '0010', 'A001', 10 ,'20070430'
union all select '0020' ,'A002', 20 ,'20070430'declare @b table(a varchar(6),b varchar(6),c varchar(10),d varchar(10),e varchar(10))
insert @b select '0010', 'A001', 'a1', 5 ,'20070430'
union all select '0010', 'A001', 'b1', 2 ,'20070430'
union all select '0010', 'A001', 'c1', 3 ,'20070430'
union all select '0020', 'A002', 'a2', 15 ,'20070430'
union all select '0020', 'A002', 'b2', 5 ,'20070430'select a,b,c,d from(
select *,e=a from @a
union all
select b,d,c,e,a from @b )
dd
order by e,a
select a,b,c,a1,d from(
select a,b,c,'' as a1,d,'' as asdf from @a
union all
select '' as bcd,b,d,c,e,a from @b )
dd
order by b,a1,a
--SQL Server 2005上测试,和你要求的结果一样
select a,b,c,'' as a1,d from @a
union all
select '' as bcd,b,d,c,e from @b )
dd
order by b,a1,a
--上面有一个字段多余,又改了一下