现在有如下表
areacode,serverName,a1,a2,a3,a4
1,2,3,4,4,4
1,3,3,3,3,3
1,4,5,5,5,5
2,2,3,3,3,3
2,3,3,3,4,4
2,4,4,4,3,6
3,2,2,2,2,2
3,4,4,4,3,6
3,4,4,4,3,6
一个地区列,一个服务列,另外是几个数字列
现在要统计的显示结果是如下,即对每个地区作统计显示,不对服务列做分组统计,
1, 2, 3, 4, 4, 4
1, 3, 3, 3, 3, 3
1, 4, 5, 5, 5, 5
1,合计, 11,12,12,12
2, 2, 3, 3, 3, 3
2, 3, 3, 3, 4, 4
2, 4, 4, 4, 3, 6
2,合计, 10,10,10,13
3, 2, 2, 2, 2, 2
3, 3, 4, 4, 3, 6
3, 4, 4, 4, 3, 6
3,合计, 10,10,8,14
不知道考虑用cube和roleup能不能实现,如何实现,请高手不吝赐教,谢谢
areacode,serverName,a1,a2,a3,a4
1,2,3,4,4,4
1,3,3,3,3,3
1,4,5,5,5,5
2,2,3,3,3,3
2,3,3,3,4,4
2,4,4,4,3,6
3,2,2,2,2,2
3,4,4,4,3,6
3,4,4,4,3,6
一个地区列,一个服务列,另外是几个数字列
现在要统计的显示结果是如下,即对每个地区作统计显示,不对服务列做分组统计,
1, 2, 3, 4, 4, 4
1, 3, 3, 3, 3, 3
1, 4, 5, 5, 5, 5
1,合计, 11,12,12,12
2, 2, 3, 3, 3, 3
2, 3, 3, 3, 4, 4
2, 4, 4, 4, 3, 6
2,合计, 10,10,10,13
3, 2, 2, 2, 2, 2
3, 3, 4, 4, 3, 6
3, 4, 4, 4, 3, 6
3,合计, 10,10,8,14
不知道考虑用cube和roleup能不能实现,如何实现,请高手不吝赐教,谢谢
declare @test table(areacode int,serverName varchar(20),a1 int,a2 int,a3 int,a4 int)
insert into @test
select 1,'2',3,4,4,4 union all
select 1,'3',3,3,3,3 union all
select 1,'4',5,5,5,5 union all
select 2,'2',3,3,3,3 union all
select 2,'3',3,3,4,4 union all
select 2,'4',4,4,3,6 union all
select 3,'2',2,2,2,2 union all
select 3,'4',4,4,3,6 union all
select 3,'4',4,4,3,6select *
from (
select case when (grouping(areacode)=1) then '-1' else areacode end as 'areacode',
case when (grouping(serverName)=1) then '合计' else serverName end as 'serverName',
sum(a1) as 'a1',
sum(a2) as 'a2',
sum(a3) as 'a3',
sum(a4) as 'a4'
from @test
group by areacode,serverName
with rollup
) t
where areacode<>-1areacode serverName a1 a2 a3 a4
----------- -------------------- ----------- ----------- ----------- -----------
1 2 3 4 4 4
1 3 3 3 3 3
1 4 5 5 5 5
1 合计 11 12 12 12
2 2 3 3 3 3
2 3 3 3 4 4
2 4 4 4 3 6
2 合计 10 10 10 13
3 2 2 2 2 2
3 4 8 8 6 12
3 合计 10 10 8 14(所影响的行数为 11 行)
这是sql server版的……在oracle里可能有些变化……你自己修改吧……
areacode,CityDesc,serverName,serverId,a1,a2,a3,a4 这个时候出现的结果就不是您上面说的这样了
2 select areacode, nvl(to_char(serverName),'合计') as serName, sum(a1)sa1, sum(a2)sa2, sum(a3)sa3, sum(a4)sa4 from test_10
3 group by rollup(areacode,serverName)
4 ) where areacode is not null
5 / AREACODE SERNAME SA1 SA2 SA3 SA4
--------------- --------------- ---------- ---------- ---------- ----------
1 2 3 4 4 4
1 3 3 3 3 3
1 4 5 5 5 5
1 合计 11 12 12 12
2 2 3 3 3 3
2 3 3 3 4 4
2 4 4 4 3 6
2 合计 10 10 10 13
3 2 2 2 2 2
3 4 8 8 6 12
3 合计 10 10 8 14没有看懂楼主想要得是什么呢
insert into test
select 1,'2',3,4,4,4 from dual union all
select 1,'3',3,3,3,3 from dual union all
select 1,'4',5,5,5,5 from dual union all
select 2,'2',3,3,3,3 from dual union all
select 2,'3',3,3,4,4 from dual union all
select 2,'4',4,4,3,6 from dual union all
select 3,'2',2,2,2,2 from dual union all
select 3,'4',4,4,3,6 from dual union all
select 3,'4',4,4,3,6 from dual
commit; select areacode, serName, sa1,sa2,sa3,sa4 from (
select areacode, nvl(to_char(serverName),'合计') as serName, sum(a1)sa1, sum(a2)sa2, sum(a3)sa3, sum(a4)sa4 from test
group by rollup(areacode,serverName)
) where areacode is not null;
drop table test;
(Oracle 版本)