oracle根据3个分组，相同数据显示为空，算出3个小计，最后算出总计

根据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);

17 个解决方案

#1

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

#2

引用 1 楼 cappuccinoc 的回复:

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

#3

方法大致差不多 下面是查询结果

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)

#4

各位大哥，如果我要加带条件查询，应该在代码什么地方加，谢谢

#5

要加什么条件 这样的？

from T_Goods
where gid = 'A01' and ... 
group by rollup(gid,gname,gcolour,gsize)

#6

我是多表，想把这段代码放进视图在视图外面加条件查询。

#7

--创建视图 将查询结果插入视图
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 ...

#8

代码放进视图，然后查询视图就没有代码的效果，只是普通带条件查询


如何把这段代码放进视图，查询视图有这种效果

#9

那这个视图设计 明显就矛盾了  

因为在视图里面放的总计是 所有的总计

所以查询视图显示出来的 肯定就是所有的总计了 不可能因为你的过滤 导致总计数据发生变化 

如果需要这种格式 那只能支持查询 1行到gid小计了

#10

如果需要这种过滤 可以考虑在数据里面 添加辅助字段 显示的时候不要显示

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' 

 

#11

解决没？  

先把能通过的流程弄清楚 思路理出来  然后再发出来解决 

#12

嗯，多谢，解决了。
我换视图改这段代码为什么报 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
select

rownum 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小计
帮帮忙，马上结贴

#13

视图里面的语句应该没问题吧？

问题可能出现在2个位置：
查询视图中longdesc和qty是不是都为数字
 decode(sizedesc,null,null,sum(longdesc)) longdesc是不是sizedesc和sum()冲突了

如果都正确的话  那使用排除法 

将查询条件中一个一个去掉 看是哪个点出错 再具体解决

#14

是数字，视图里面字段15个
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段，不知道如何改了

#15

 顶顶呀  顶顶呀  顶顶呀

#16

引用 14 楼 u010669156 的回复:

是数字，视图里面字段15个
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,

#17

ok了，万分感谢