求高人指点这种结果的语句代码,谢谢
这是我的数据结构
create table T_Goods
(
Id int primary key,
GId varchar2(10) not null,
GName varchar2(20) not null,
GColour varchar2(10),
GWithin int,
GSize varchar2(10),
GNumber int
) CREATE SEQUENCE seq_goods
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;
insert into T_Goods values(seq_goods.nextval,'A01','上衣','红',0,'S',10);
insert into T_Goods values(seq_goods.nextval,'A01','上衣','白',0,'M',20);
insert into T_Goods values(seq_goods.nextval,'A01','上衣','黑',0,'L',30);
insert into T_Goods values(seq_goods.nextval,'A02','下衣','红',0,'S',5);
insert into T_Goods values(seq_goods.nextval,'A02','下衣','白',0,'M',15);
这是我的数据结构
create table T_Goods
(
Id int primary key,
GId varchar2(10) not null,
GName varchar2(20) not null,
GColour varchar2(10),
GWithin int,
GSize varchar2(10),
GNumber int
) CREATE SEQUENCE seq_goods
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;
insert into T_Goods values(seq_goods.nextval,'A01','上衣','红',0,'S',10);
insert into T_Goods values(seq_goods.nextval,'A01','上衣','白',0,'M',20);
insert into T_Goods values(seq_goods.nextval,'A01','上衣','黑',0,'L',30);
insert into T_Goods values(seq_goods.nextval,'A02','下衣','红',0,'S',5);
insert into T_Goods values(seq_goods.nextval,'A02','下衣','白',0,'M',15);
select rownum seq,
decode(rn, 1, gid) gid,
decode(rn, 1, gname) gname,
gcolour,
gwithin,
gsize,
gnumber
from (select t.*, row_number() over(partition by gid order by gnumber) rn
from (select GId,
gname,
gcolour,
gwithin,
gsize,
sum(gnumber) gnumber
from t_goods
group by GId, gname, gcolour, gwithin, gsize
union all
select gid, gname, null, null, '小计', sum(gnumber)
from t_goods
group by gid, gname
union all
select null, null, null, null, '总计', sum(gnumber)
from t_goods) t);
SEQ GID GNAME GCOLOUR GWITHIN GSIZE GNUMBER
---------- ---------- -------------------- ---------- ---------- ---------- --------
1 A01 上衣 红 0 S 10
2 白 0 M 20
3 黑 0 L 30
4 小计 60
5 A02 下衣 红 0 S 5
6 白 0 M 15
7 小计 20
8 A03 下衣 红 0 S 5
9 白 0 M 15
10 小计 20
11 总计 100
SUM(case GSIZE when 'S' then GNUMBER end )as SGNUMBER,
SUM(case GSIZE when 'M' then GNUMBER end )as MGNUMBER,
SUM(case GSIZE when 'L' then GNUMBER end )as LGNUMBER
FROM TEST GROUP BY GID,GNAME
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 * from T_Goods1;
with t as
(select GId, gname, gcolour, gwithin, gsize, sum(gnumber) gnumber
from T_Goods1
group by GId, gname, gcolour, gwithin, gsize
union all
select gid, gname,gcolour, null, 'gcolour小计', sum(gnumber)
from T_Goods1
group by gid, gname,gcolour
union all
select gid, gname, null, null, 'gname小计', sum(gnumber)
from T_Goods1
group by gid, gname
union all
select null, null, null, null, '总计', sum(gnumber)
from T_Goods1
)
select rownum seq,
decode(rn, 1, gid) gid,
decode(rn2, 1, gname) gname,
gcolour,
gwithin,
gsize,
gnumber
from (
select t.*, row_number() over(partition by gid order by gid,gname,gnumber) rn,
row_number() over(partition by gid,gname order by gid,gname,gnumber) rn2 from t);
with t as
(select GId, gname, gcolour, gwithin, gsize, sum(gnumber) gnumber
from T_Goods
group by GId, gname, gcolour, gwithin, gsize
union all
select gid, gname,gcolour, null, 'gcolour小计', sum(gnumber)
from T_Goods
group by gid, gname,gcolour
union all
select gid, gname, null, null, 'gname小计', sum(gnumber)
from T_Goods
group by gid, gname
union all
select null, null, null, null, '总计', sum(gnumber)
from T_Goods
)
select rownum seq,
decode(rn, 1, gid) gid,
decode(rn2, 1, gname) gname,
gcolour,
gwithin,
gsize,
gnumber
from (
select t.*, row_number() over(partition by gid order by gid,gname,gnumber) rn,
row_number() over(partition by gid,gname order by gid,gname,gnumber) rn2 from t);