求高人指点这种结果的语句代码,谢谢
这是我的数据结构
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);
解决方案 »
- 求解 java.sql.BatchUpdateException: ORA-01461: 仅能绑定要插入 LONG 列的 LONG 值
- Oracle客户端的enterprise manager console登陆闪一下就关闭
- 看看这个SQL有多少选错的?
- 如何去oracle 获取列名和该列的值
- 关于oracle的安装为题
- 关于自身连接的问题?这样为什么结果不一样?
- DBMS_REPCAT_AUTH程序包
- 我想判断两个日期的大小,精确到小时,有什么好的函数啊?
- 疑问:Oracle8.0 FOR NT 与Windows2000不兼容????????????????
- 有没有比较难的SQL
- 求大神教教小弟怎么在oracle管理页面添加一个控制存储过程的JOB
- 关于存储过程的编译问题
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);