根据3个分组，相同数据显示为空，算出3个小计，最后算出总计
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);
------解决方案--------------------
select row_number() over(order by gid, gname, gcolour, gsize) num,
decode(row_number()
over(partition by gid order by gid, gname, gcolour,gsize),
'1',
gid,
'') gid1,
decode(row_number()
over(partition by gid,gname order by gid,gname, gcolour,gsize),
'1',
gname,
'') gname1,
decode(row_number()
over(partition by gid, gname,gcolour order by gid, gname, gcolour,gsize),
'1',
gcolour,
'') gcolour1,
case
when gid is not null and gname is null and gcolour is null then
'gid小计'
when gid is not null and gname is not null and gcolour is null then
'gname小计'
when gid is not null and gname is not null and gcolour is not null and
gsize is null then
'gcolour小计'
when gid is null and gname is null then
'总计'
else
gsize
end gsize1,
sum(gwithin) gwithin,
sum(gnumber) gnumber
from t_goods t
group by rollup(gid, gname, gcolour, (gwithin, gsize, gnumber))
order by gid, gname, gcolour, gsize
------解决方案--------------------