跪求各位热心大哥帮忙看下什么错
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进行分组，分组完成算出小计，我现在的语句的错误是，小计的顺序错了
想达到这种效果，相同货品编号，名称显示为空，分组算出小计，最后算出总计
[解决办法]
--创建测试表
create table RR810101
(
CHANNELNAME VARCHAR2(60),
GOODSNAME VARCHAR2(60),
COLORDESC VARCHAR2(60),
LONGDESC NUMBER,
DPPRICE NUMBER,
SIZEDESC NUMBER,
SIZEQTY NUMBER
);
--增加测试数据
insert into rr810101 (CHANNELNAME, GOODSNAME, COLORDESC, LONGDESC, DPPRICE, SIZEDESC, SIZEQTY)
values ('A0003', '上衣33', '红', 110, 120, 175, 12);
insert into rr810101 (CHANNELNAME, GOODSNAME, COLORDESC, LONGDESC, DPPRICE, SIZEDESC, SIZEQTY)
values ('A0003', '上衣333', '红', 110, 120, 175, 12);
insert into rr810101 (CHANNELNAME, GOODSNAME, COLORDESC, LONGDESC, DPPRICE, SIZEDESC, SIZEQTY)
values ('A0001', '上衣1', '白', 80, 100, 165, 10);
insert into rr810101 (CHANNELNAME, GOODSNAME, COLORDESC, LONGDESC, DPPRICE, SIZEDESC, SIZEQTY)
values ('B0001', '裤子1', '黑', 90, 120, 170, 10);
insert into rr810101 (CHANNELNAME, GOODSNAME, COLORDESC, LONGDESC, DPPRICE, SIZEDESC, SIZEQTY)
values ('A0002', '上衣1', '黑', 90, 110, 170, 11);
insert into rr810101 (CHANNELNAME, GOODSNAME, COLORDESC, LONGDESC, DPPRICE, SIZEDESC, SIZEQTY)
values ('A0003', '上衣3', '红', 110, 120, 175, 12);
insert into rr810101 (CHANNELNAME, GOODSNAME, COLORDESC, LONGDESC, DPPRICE, SIZEDESC, SIZEQTY)
values ('B0002', '裤子2', '黑', 90, 120, 170, 10);
insert into rr810101 (CHANNELNAME, GOODSNAME, COLORDESC, LONGDESC, DPPRICE, SIZEDESC, SIZEQTY)
values ('B0003', '裤子3', '黑', 90, 120, 170, 10);
insert into rr810101 (CHANNELNAME, GOODSNAME, COLORDESC, LONGDESC, DPPRICE, SIZEDESC, SIZEQTY)
values ('A0003', '上衣3', '红', 90, 120, 165, 12);
insert into rr810101 (CHANNELNAME, GOODSNAME, COLORDESC, LONGDESC, DPPRICE, SIZEDESC, SIZEQTY)
values ('A0003', '上衣3', '红', 100, 120, 170, 12);
--查询数据
select rownum num, channelname, goodsname, colordesc, dpprice, sizeqty
from (select case
when channelname is null and goodsname is null then
'1'
else
'2'
end xh1,
case
when channelname is not null and goodsname is null then
'1'
else
'2'
end xh2,
case
when channelname is not null and goodsname is not null and
colordesc is null then
'1'
else
'2'
end xh3,
case
when channelname is not null and goodsname is not null and
colordesc is not null then
channelname
else
''
end channelname,
case
when channelname is not null and goodsname is not null and
colordesc is not null then
goodsname
else
''
end goodsname,
case
when channelname is not null and goodsname is not null and
colordesc is null then
goodsname
[解决办法]
'_小计'
when channelname is not null and goodsname is null then
channelname
[解决办法]
'_小计'
when channelname is null and goodsname is null then
channelname
[解决办法]
'总计'
else
colordesc
end colordesc,
sum(longdesc) longdesc,
sum(dpprice) dpprice,
sum(sizeqty) sizeqty,
channelname channelname1,
goodsname goodsname1
from rr810101 t
group by rollup(channelname,
goodsname,
(rownum, colordesc, longdesc, dpprice, sizedesc,
sizeqty))
order by xh1 desc, channelname1, xh2 desc, goodsname1, xh3 desc)
select rownum seq,
decode(rn, 1, channelname) channelname,
decode(rn, 1, goodsname) goodsname,
colordesc,
dpprice1,
sizedesc1,
sizeqty1
from (
select t.channelname,t.goodsname,t.colordesc,t.dpprice1,t.sizedesc1,t.sizeqty1,
row_number() over(partition by channelname,goodsname order by colordesc) rn
from (select channelname, goodsname, colordesc,
sum(dpprice) dpprice1, sum(sizedesc) sizedesc1, sum(sizeqty) sizeqty1
from RR810101
group by channelname,goodsname,colordesc
union all
select channelname, goodsname, '小计', sum(dpprice),sum(sizedesc), sum(sizeqty)
from RR810101
group by channelname, goodsname
union all
select null, null, '总计', sum(dpprice),sum(sizedesc),sum(sizeqty) from RR810101) t
);
11A0001上衣1白10016510
22小计10016510
33A0002上衣1黑11017011
44小计11017011
55A0003上衣3红36051036
66小计36051036
77A0003上衣33红12017512
88小计12017512
99A0003上衣333红12017512
1010小计12017512
1111B0001裤子1黑12017010
1212小计12017010
1313B0002裤子2黑12017010
1414小计12017010
1515B0003裤子3黑12017010
1616小计12017010
1717总计11701705111
