select
row_number() over( order by channelname,num) num,
decode(row_number() over(partition by channelname order by channelname,num),'1',channelname,'') channelname,
decode(row_number() over(partition by goodsname order by goodsname,num),'1',goodsname,'') goodsname,
colordesc colordesc,
longdesc longdesc,
dpprice dpprice,
case when num is null and channelname is not null then '小计'
when num is null and channelname is null then '总计'
else sizedesc end sizedesc,
sum(sizeqty) sizeqty
from rr810101 t
group by rollup(channelname, (num, channelname, goodsname, colordesc, longdesc, dpprice,sizedesc,sizeqty))
order by channelname,goodsname,num
我的数据结构 视图 num是一个nownum的伪字段create or replace view rr810101 as
select
rownum num,a.channelid channelname,a.GOODSId goodsname,a.COLORID as colordesc,a.longid longdesc,dpprice,d.id sizedesc
,sum((case when d.FILEDNAME='S1' then a.s1 when d.FILEDNAME='S2' then a.s2 when d.FILEDNAME='S3' then a.s3 when d.FILEDNAME='S4' then a.s4 when d.FILEDNAME='S5' then a.s5
when d.FILEDNAME='S6' then a.s6 when d.FILEDNAME='S7' then a.s7 when d.FILEDNAME='S8' then a.s8 when d.FILEDNAME='S9' then a.s9 when d.FILEDNAME='S10' then a.s10
when d.FILEDNAME='S11' then a.s11 when d.FILEDNAME='S12' then a.s12 when d.FILEDNAME='S13' then a.s13 when d.FILEDNAME='S14' then a.s14 when d.FILEDNAME='S15' then a.s15
when d.FILEDNAME='S46' then a.s46 when d.FILEDNAME='S47' then a.s47 when d.FILEDNAME='S48' then a.s48 when d.FILEDNAME='S49' then a.s49 else a.s50 end)) as sizeQty
from viewdeliverysheetgoods a,channel b,goods c, sizecategory d
where --a.guid='37eea67228b4b2d6edc92cdd2b9017db' and
a.channelid=b.channelid and a.GOODSID=c.goodsid and c.sizecategoryid=d.sizecategoryid
group by rownum,a.channelid,a.GOODSId,a.COLORID,a.longid,dpprice,d.id
order by ROWNUM, channelname,goodsname,colordesc;我是根据channelname,goodsname进行分组,分组完成算出小计,我现在的语句的错误是,小计的顺序错了
想达到这种效果,相同货品编号,名称显示为空,分组算出小计,最后算出总计
row_number() over( order by channelname,num) num,
decode(row_number() over(partition by channelname order by channelname,num),'1',channelname,'') channelname,
decode(row_number() over(partition by goodsname order by goodsname,num),'1',goodsname,'') goodsname,
colordesc colordesc,
longdesc longdesc,
dpprice dpprice,
case when num is null and channelname is not null then '小计'
when num is null and channelname is null then '总计'
else sizedesc end sizedesc,
sum(sizeqty) sizeqty
from rr810101 t
group by rollup(channelname, (num, channelname, goodsname, colordesc, longdesc, dpprice,sizedesc,sizeqty))
order by channelname,goodsname,num
我的数据结构 视图 num是一个nownum的伪字段create or replace view rr810101 as
select
rownum num,a.channelid channelname,a.GOODSId goodsname,a.COLORID as colordesc,a.longid longdesc,dpprice,d.id sizedesc
,sum((case when d.FILEDNAME='S1' then a.s1 when d.FILEDNAME='S2' then a.s2 when d.FILEDNAME='S3' then a.s3 when d.FILEDNAME='S4' then a.s4 when d.FILEDNAME='S5' then a.s5
when d.FILEDNAME='S6' then a.s6 when d.FILEDNAME='S7' then a.s7 when d.FILEDNAME='S8' then a.s8 when d.FILEDNAME='S9' then a.s9 when d.FILEDNAME='S10' then a.s10
when d.FILEDNAME='S11' then a.s11 when d.FILEDNAME='S12' then a.s12 when d.FILEDNAME='S13' then a.s13 when d.FILEDNAME='S14' then a.s14 when d.FILEDNAME='S15' then a.s15
when d.FILEDNAME='S46' then a.s46 when d.FILEDNAME='S47' then a.s47 when d.FILEDNAME='S48' then a.s48 when d.FILEDNAME='S49' then a.s49 else a.s50 end)) as sizeQty
from viewdeliverysheetgoods a,channel b,goods c, sizecategory d
where --a.guid='37eea67228b4b2d6edc92cdd2b9017db' and
a.channelid=b.channelid and a.GOODSID=c.goodsid and c.sizecategoryid=d.sizecategoryid
group by rownum,a.channelid,a.GOODSId,a.COLORID,a.longid,dpprice,d.id
order by ROWNUM, channelname,goodsname,colordesc;我是根据channelname,goodsname进行分组,分组完成算出小计,我现在的语句的错误是,小计的顺序错了
想达到这种效果,相同货品编号,名称显示为空,分组算出小计,最后算出总计
select
row_number() over( order by channelname,num) num,
decode(row_number() over(partition by channelname order by channelname,num),'1',channelname,'') channelname,
decode(row_number() over(partition by goodsname order by goodsname,num),'1',goodsname,'') goodsname,
colordesc colordesc,
longdesc longdesc,
dpprice dpprice,
case when num is null and channelname is not null then '小计'
when num is null and channelname is null then '总计'
else to_char(sizedesc) end sizedesc,
sum(sizeqty) sizeqty,
channelname channelname1,
goodsname goodsname1
from rr810101 t
group by rollup(channelname, (num, channelname, goodsname, colordesc, longdesc, dpprice,sizedesc,sizeqty))
order by channelname1,goodsname1,num
create table T_Goods
(
GId varchar2(10) not null,
GName varchar2(20) not null,
GColour varchar2(10),
GWithin int,
GSize varchar2(10),
GNumber int
) insert into T_Goods values('A01','上衣','红',0,'S',10);
insert into T_Goods values('A01','上衣','红',0,'M',20);
insert into T_Goods values('A01','上衣','白',0,'L',30);
insert into T_Goods values('A01','下衣','红',0,'S',10);
insert into T_Goods values('A01','下衣','白',0,'M',20);
insert into T_Goods values('A01','下衣','黑',0,'L',30);
insert into T_Goods values('A02','上衣','红',0,'S',5);
insert into T_Goods values('A02','上衣','白',0,'M',15);
insert into T_Goods values('A02','下衣','红',0,'S',5);
insert into T_Goods values('A02','下衣','白',0,'M',15);