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进行分组,分组完成算出小计,我现在的语句的错误是,小计的顺序错了
想达到这种效果,相同货品编号,名称显示为空,分组算出小计,最后算出总计
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
);1 1 A0001 上衣1 白 100 165 10
2 2 小计 100 165 10
3 3 A0002 上衣1 黑 110 170 11
4 4 小计 110 170 11
5 5 A0003 上衣3 红 360 510 36
6 6 小计 360 510 36
7 7 A0003 上衣33 红 120 175 12
8 8 小计 120 175 12
9 9 A0003 上衣333 红 120 175 12
10 10 小计 120 175 12
11 11 B0001 裤子1 黑 120 170 10
12 12 小计 120 170 10
13 13 B0002 裤子2 黑 120 170 10
14 14 小计 120 170 10
15 15 B0003 裤子3 黑 120 170 10
16 16 小计 120 170 10
17 17 总计 1170 1705 111
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);