![]() |
#2
shezhenhui19892008-04-07 15:02
|
表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
只有本站会员才能查看附件,请 登录
只有本站会员才能查看附件,请 登录