select 地区,物料分类, sum(目标数量) from tb1 group by 地区,物料分类
create table ccc(deptid varchar(10),custid varchar(10),clsid varchar(10),amount money) insert into ccc values ('1','1','1',100) insert into ccc values ('1','2','1',200) insert into ccc values ('1','2','3',300) insert into ccc values ('2','1','1',100) insert into ccc values ('2','3','2',200) insert into ccc values ('2','3','3',100) insert into ccc values ('3','2','2',500)select * from ( select deptid,custid,clsid,sum(amount) as fsum from ccc group by deptid,custid,clsid with cube ) t1 where deptid is not null order by isnull(deptid,'ZZ'),isnull(custid,'ZZ'),isnull(clsid ,'ZZ')应该就可以得到结果了,再把格式改改就行了。
insert into ccc values ('1','1','1',100)
insert into ccc values ('1','2','1',200)
insert into ccc values ('1','2','3',300)
insert into ccc values ('2','1','1',100)
insert into ccc values ('2','3','2',200)
insert into ccc values ('2','3','3',100)
insert into ccc values ('3','2','2',500)select *
from (
select deptid,custid,clsid,sum(amount) as fsum
from ccc
group by deptid,custid,clsid
with cube
) t1
where deptid is not null
order by isnull(deptid,'ZZ'),isnull(custid,'ZZ'),isnull(clsid ,'ZZ')应该就可以得到结果了,再把格式改改就行了。