根据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);
(
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);
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
select decode(gid,lag(gid) over(order by gid),null,gid) gid,
decode(gname,lag(gname) over(order by gid,gname),null,gname) gname,
decode(gcolour,lag(gcolour) over(order by gid,gname,gcolour),null,gcolour) gcolour,
decode(gsize,null,null,sum(GWITHIN)) GWITHIN,
case
when gsize is null and gid is not null and gname is not null and gcolour is not null then 'gcolour小计'
when gcolour is null and gsize is null and gid is not null and gname is not null then 'gname小计'
when gsize is null and gid is not null and gname is null and gcolour is null then 'gid小计'
when gsize is null and gid is null and gname is null and gcolour is null then '总计'
else gsize end gsize,
sum(GNUMBER) GNUMBER
from T_Goods
group by rollup(gid,gname,gcolour,gsize)
where gid = 'A01' and ...
group by rollup(gid,gname,gcolour,gsize)
--创建视图 将查询结果插入视图
create or replace view view_test as
select decode(gid,lag(gid) over(order by gid),null,gid) gid,
decode(gname,lag(gname) over(order by gid,gname),null,gname) gname,
decode(gcolour,lag(gcolour) over(order by gid,gname,gcolour),null,gcolour) gcolour,
decode(gsize,null,null,sum(GWITHIN)) GWITHIN,
case
when gsize is null and gid is not null and gname is not null and gcolour is not null then 'gcolour小计'
when gcolour is null and gsize is null and gid is not null and gname is not null then 'gname小计'
when gsize is null and gid is not null and gname is null and gcolour is null then 'gid小计'
when gsize is null and gid is null and gname is null and gcolour is null then '总计'
else gsize end gsize,
sum(GNUMBER) GNUMBER
from T_Goods
group by rollup(gid,gname,gcolour,gsize);
--访问视图
select *
from view_test
where ...
create or replace view view_test as
select gid g_id, --方便查询使用
decode(gid,lag(gid) over(order by gid),null,gid) gid,
......select gid,gname,gcolour,gwithin,gsize,gnumber
from view_test
where g_id = 'A01'
我换视图改这段代码为什么报 ORA-12704:字符集不匹配我的代码
select decode(PurSheetguid,lag(PurSheetguid) over(order by PurSheetguid),null,PurSheetguid) PurSheetguid,
decode(sheetid,lag(sheetid) over(order by PurSheetguid,sheetid),null,sheetid) sheetid,
decode(abbrev,lag(abbrev) over(order by PurSheetguid,sheetid,abbrev),null,abbrev) abbrev,
decode(sizedesc,null,null,sum(longdesc)) longdesc,
case
when sizedesc is null and PurSheetguid is not null and sheetid is not null and abbrev is not null then 'gcolour小计'
when abbrev is null and sizedesc is null and PurSheetguid is not null and sheetid is not null then 'gname小计'
when sizedesc is null and PurSheetguid is not null and sheetid is null and abbrev is null then 'gid小计'
when sizedesc is null and PurSheetguid is null and sheetid is null and abbrev is null then '总计'
else sizedesc end sizedesc,
sum(qty) qty
from rr810102
group by rollup(PurSheetguid,sheetid,abbrev,sizedesc)我的视图
create or replace view rr810102 as
selectrownum num,
(select sheetid from PurSheet where guid=a.pursheetid) PurSheetguid,
a.sheetid,e.abbrev,a.createddate,c.goodsid,c.colorid,c.longid,d.goodsno,
(select colorcode from color where id = c.colorid) colorcode,
nvl((select colordesc from goodscolordesc where goodsid = c.goodsid and colorid = c.colorid),(select colordesc from color where id = c.colorid)) colordesc,
(select longdesc from dictlong where id = c.longid) longdesc,
g.id gid,g.SIZEDESC,
(case when g.FILEDNAME='S1' then c.s1
when g.FILEDNAME='S2' then c.s2 when g.FILEDNAME='S3' then c.s3 when g.FILEDNAME='S4' then c.s4
when g.FILEDNAME='S5' then c.s5 when g.FILEDNAME='S6' then c.s6 when g.FILEDNAME='S7' then c.s7
when g.FILEDNAME='S8' then c.s8 when g.FILEDNAME='S9' then c.s9 when g.FILEDNAME='S10' then c.s10
when g.FILEDNAME='S11' then c.s11 when g.FILEDNAME='S12' then c.s12 when g.FILEDNAME='S13' then c.s13
when g.FILEDNAME='S14' then c.s14 when g.FILEDNAME='S15' then c.s15 when g.FILEDNAME='S16' then c.s16
when g.FILEDNAME='S17' then c.s17 when g.FILEDNAME='S18' then c.s18 when g.FILEDNAME='S19' then c.s19
when g.FILEDNAME='S20' then c.s20 when g.FILEDNAME='S21' then c.s21 when g.FILEDNAME='S22' then c.s22
when g.FILEDNAME='S23' then c.s23 when g.FILEDNAME='S24' then c.s24 when g.FILEDNAME='S25' then c.s25
when g.FILEDNAME='S26' then c.s26 when g.FILEDNAME='S27' then c.s27 when g.FILEDNAME='S28' then c.s28
when g.FILEDNAME='S29' then c.s29 when g.FILEDNAME='S30' then c.s30 when g.FILEDNAME='S31' then c.s31
when g.FILEDNAME='S32' then c.s32 when g.FILEDNAME='S33' then c.s33 when g.FILEDNAME='S34' then c.s34
when g.FILEDNAME='S35' then c.s35 when g.FILEDNAME='S36' then c.s36 when g.FILEDNAME='S37' then c.s37
when g.FILEDNAME='S38' then c.s38 when g.FILEDNAME='S39' then c.s39 when g.FILEDNAME='S40' then c.s40
when g.FILEDNAME='S41' then c.s41 when g.FILEDNAME='S42' then c.s42 when g.FILEDNAME='S43' then c.s43
when g.FILEDNAME='S44' then c.s44 when g.FILEDNAME='S45' then c.s45 when g.FILEDNAME='S46' then c.s46
when g.FILEDNAME='S47' then c.s47 when g.FILEDNAME='S48' then c.s48 when g.FILEDNAME='S49' then c.s49
else c.s50 end) qty
from STOCKINSHEET a,
STOCKINSHEETGOODS b,
STOCKINSHEETDETAIL c,
goods d,
channel e,
sizecategory g
where a.guid = b.guid
and a.guid = c.guid
and b.code = c.code(+)
and b.goodsid = d.goodsid(+)
and a.channelid = e.channelid(+)
and d.sizecategoryid=g.sizecategoryid
order by goodsno;字段有num,pursheetguid,sheetid,abbrev,createddate ,goodsid ,colorid ,longid goodsno,colorcode ,colordesc,longdesc ,gid ,sizedesc,qty根据pursheetguid,sheetid,abbrev,goodsno分组算出各qty小计
帮帮忙,马上结贴
查询视图中longdesc和qty是不是都为数字
decode(sizedesc,null,null,sum(longdesc)) longdesc是不是sizedesc和sum()冲突了如果都正确的话 那使用排除法 将查询条件中一个一个去掉 看是哪个点出错 再具体解决
num,pursheetguid,sheetid,abbrev,createddate ,goodsid ,colorid ,longid goodsno,colorcode ,colordesc,longdesc ,gid ,sizedesc,qty
longdesc和qty是数字型
decode(sizedesc,null,null,sum(longdesc)) longdesc, else sizedesc end sizedesc,
sum(qty) qty
from rr810102
group by rollup(PurSheetguid,sheetid,abbrev,sizedesc)
这2段,不知道如何改了
to_char转换一下看看
decode(gsize,null,null,to_char(sum(GWITHIN))) GWITHIN,
else to_char(sizedesc) end sizedesc,