关于合并查询结果的问题请教大家，谢谢!
有一数据表AAA，字段分别是 MingChen 、 ShuLian 、 WanChenShiJin ，想根据WanChenShiJin 是否为 null 来分类查询数据并合并查询结果：当 WanChenShiJin 为 null 时是属于“在建工程”，当 WanChenShiJin 不为 null 时是属于“已完工程”，并且分开计算出“在建工程”和“已完工程”中 ShuLian 字段 的合计数。表AAA
MingChen ShuLian WanChenShiJin
A 100.11 null
B 200.22 2006-6-6
C 300.33 null
D 400.44 2007-7-7
想得到如下查询结果：
MingChen ShuLian WanChenShiJin
在建工程 null null
B 200.22 2006-6-6
D 400.44 2007-7-7
小计 600.66 null
已完工程 null null
A 100.11 null
C 300.33 null
小计 400.44 null
查询语句如下：
Select '在建工程 ' as MingChen,null as ShuLian,null as WanChenShiJin
union
select MingChen,ShuLian,WanChenShiJin from AAA where WanChenShiJin is not null
union
select '小计' as MingChen,sum(ShuLian) as ShuLian,null WanChenShiJin
from AAA where WanChenShiJin is not null
union
select '已完工程' as MingChen,null as ShuLian,null as WanChenShiJin
union
select MingChen,ShuLian,WanChenShiJin from AAA where WanChenShiJin is null
union
select '小计' as MingChen,sum(ShuLian) as ShuLian,null WanChenShiJin
from AAA where WanChenShiJin is null
但上述语句查询出来的结果是
MingChen ShuLian WanChenShiJin
A 100.11 null
B 200.22 2006-6-6
C 300.33 null
D 400.44 2007-7-7
小计 400.44 null
小计 600.66 null
已完工程 null null
在建工程 null null
虽然查询出来的结果值没错，但“在建工程”、“已完工程”、两个“小计”数据行并没有排在想要的地方。
请大家帮忙如何写查询语句才能得到想要的数据显示结果？？？谢谢!
在SQL查询分析器中的语句如下：
create table AAA
(
MingChen varchar(80) null,
ShuLian decimal(18,2) not null,
WanChenShiJin smalldatetime null
)
Insert into AAA(MingChen,ShuLian,WanChenShiJin) values('A',100.11,null)
Insert into AAA(MingChen,ShuLian,WanChenShiJin) values('B',200.22,'2006-6-6')
Insert into AAA(MingChen,ShuLian,WanChenShiJin) values('C',300.33,null)
Insert into AAA(MingChen,ShuLian,WanChenShiJin) values('D',400.44,'2007-7-7')
Select '在建工程 ' as MingChen,null as ShuLian,null as WanChenShiJin
union
select MingChen,ShuLian,WanChenShiJin from AAA where WanChenShiJin is not null
union
select '小计' as MingChen,sum(ShuLian) as ShuLian,null WanChenShiJin
from AAA where WanChenShiJin is not null
union
select '已完工程' as MingChen,null as ShuLian,null as WanChenShiJin
union
select MingChen,ShuLian,WanChenShiJin from AAA where WanChenShiJin is null
union
select '小计' as MingChen,sum(ShuLian) as ShuLian,null WanChenShiJin
from AAA where WanChenShiJin is null
drop table AAA
