select name,sum(money) as money,(sum(money)-avg(money1)) as money1 from temp group by name
union all
select '总计',sum(money) as money,(sum(money)-avg(money1)) as money1 from temp
union all
select '总计',sum(money) as money,(sum(money)-avg(money1)) as money1 from temp
union
select '合计:' as name, sum(money) as money,(sum(money)-avg(money1)) as money1 from je
union
select '合计:' as name, sum(money) as money,(sum(money)-avg(money1)) as money1 from temp
name=(select distion from temp where name='aaa' or name=='bbb')
and
money=
(select count(money) from temp where name='aaa' or name=='bbb')
and
money1=
(select count(money1) from temp where money1=
(select money1 from temp where money1=count(money)-count(money1)
and
name="aaa" or name="bbb"))
select name=case grouping(name) when 1 then '总计:' else name end
,[money]=sum([money])
,money1=sum([money]-money1)
from [temp]
group by name with rollup
目标表中的每一行数据是这样得来的:"money"字段按"name"进行累加,"money1"字段按"name"进行求平均再与"money"累加的和相减(其实money1项中按"name"都会是一样的)
如:"aaa"的"money"是123+234=357,"money"是357-(11+11)/2=346
"bbb"的"money"是567+789+678=2034,"money"是2034-(22+22+22)/3=2012 但若用“union all
select '总计',sum(money) as money,(sum(money)-avg(money1)) as money1 from temp”或“with rollup”都得不到想要的结果
(得到的是“总计: 2391 2373.4”)
可能是我的表达不好,请大家原谅,请大家帮忙想想,谢谢!!!
go
select name,sum(money) as money,(sum(money)-avg(money1)) as money1 from temp group by name
union all
select '总计',sum(money) as money,(sum(money1)) as money1 from t
是什么意思,我用的是sql2000 server ,请帮忙解决。
sp_dboption 数据库名, 'select into/bulkcopy',true如果还出错,那你就这样吧,应该不会有问题,只不过效率可能不太好.
select name,sum(money) as money,(sum(money)-avg(money1)) as money1 from temp group by name
union all
select '总计',sum(money) as money,(sum(money1)) as money1 from
(
select name,sum(money) as money,(sum(money)-avg(money1)) as money1 from temp group by name
)a