数据库中 显示 小计 与 总计

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

 

 

 

 

 

 

 

 

 

 

 

select t.*,row_number()OVER(PARTITION by ylmc order by gh ) rn
  from  (select gh ,ylmc ,sum(syjc) ,sum(slyc) ,sum(smt) ,sum(syg) ,sum(gcls) ,sum(fmt) ,sum(fyp) ,sum(fyg) ,sum(gclf) ,sum(sysh) ,sum(fysh) ,sum(ccsh) ,sum(bygc) ,sum(byzc)
                   from zy_ylhz_two abc group by gh,ylmc
                union ALL    
                    select '小计',ylmc,sum(syjc),sum(slyc),sum(smt),sum(syg),sum(gcls),sum(fmt),sum(fyp),sum(fyg),sum(gclf),sum(sysh),sum(fysh),sum(ccsh),sum(bygc),sum(byzc)
                    from zy_ylhz_two group by ylmc
                union ALL
                    select '总计','',sum(syjc),sum(slyc),sum(smt),sum(syg),sum(gcls),sum(fmt),sum(fyp),sum(fyg),sum(gclf),sum(sysh),sum(fysh),sum(ccsh),sum(bygc),sum(byzc)
                    from zy_ylhz_two )t