关于使用rollup，只显示总计

Question

下面这个sql语句是拼出来的，想在最后一行显示一个总计，如果用GROUP BY rollup(temp.fcuid,temp.forder,temp.fixnumber,temp.fixname,temp.fname,TEMP.FTYPE)的话中间出现很多不需要的小计，查了一下资料，部分rollup只能处理不要总计，有没有办法可以不小中间的小计，只要一个总计就可以。
SELECT temp.fcuid, temp.fixnumber,temp.fixname,temp.fname,temp.forder,TEMP.FTYPE 
,round(sum(decode(fhbnumber,'KA08-001',fmoney,0)),2) "KA08-001"
,round(sum(decode(fhbnumber,'KA08-002',fmoney,0)),2) "KA08-002"
,round(sum(decode(fhbnumber,'KA08-003',fmoney,0)),2) "KA08-003"
,round(sum(decode(fhbnumber,'KA08-004',fmoney,0)),2) "KA08-004"
,round(sum(decode(fhbnumber,'KA08-005',fmoney,0)),2) "KA08-005"
,round(sum(decode(fhbnumber,'KA08-006',fmoney,0)),2) "KA08-006"
,round(sum(decode(fhbnumber,'KA08-007',fmoney,0)),2) "KA08-007"
,round(sum(decode(fhbnumber,'KA08-008',fmoney,0)),2) "KA08-008"
,round(sum(decode(fhbnumber,'KA08-009',fmoney,0)),2) "KA08-009"
,round(sum(decode(fhbnumber,'KA08-010',fmoney,0)),2) "KA08-010"
,round(sum(decode(fhbnumber,'KA08-011',fmoney,0)),2) "KA08-011"
,round(sum(decode(fhbnumber,'KA08-012',fmoney,0)),2) "KA08-012"
,round(sum(decode(fhbnumber,'KA08-013',fmoney,0)),2) "KA08-013"
,round(sum(decode(fhbnumber,'KA08-014',fmoney,0)),2) "KA08-014"
,round(sum(decode(fhbnumber,'KA08-015',fmoney,0)),2) "KA08-015"
,round(sum(decode(fhbnumber,'KA08-016',fmoney,0)),2) "KA08-016"
,round(sum(decode(fhbnumber,'KA08-L01',fmoney,0)),2) "KA08-L01"
,round(sum(decode(fhbnumber,'KA08-S01',fmoney,0)),2) "KA08-S01"
,round(sum(decode(fhbnumber,'合计',fmoney,0)),2) "合计"
FROM  t_psr_kaspecialrept temp GROUP BY temp.fcuid,temp.forder,temp.fixnumber,temp.fixname,temp.fname,TEMP.FTYPE
order by temp.forder asc

Answer 1

ROLLUP对于多列，我就觉得比较复杂了
不如UNION一句汇总行

Answer 2

另外rollup应该可以对单独一列总总计
好像是 group by rollup(列1),列2

Answer 3

HAVING(temp.fcuid is null AND temp.forder is null ANd temp.fixnumber IS NULL AND temp.fixname IS NULL 
                   AND temp.fname IS NULL AND temp.ftype IS NULL )
          OR  (temp.fcuid is NOT null AND temp.forder is NOT null ANd temp.fixnumber IS NOT NULL AND temp.fixname IS NOT NULL 
                   AND temp.fname IS NOT NULL AND temp.ftype IS NOT NULL )刚才加了这么一段代码，也可以实现。但是感觉方法太笨了

Answer 4

最后要出现这样的结果：
    NVL(TEMP.FIXNUMBER,'合计') FIXNAME FNAME KA08-001 KA08-002 KA08-003 KA08-004 KA08-005 KA08-006 KA08-007 KA08-008 KA08-009 KA08-010 KA08-011 KA08-012 KA08-013 KA08-014 KA08-015 KA08-016 KA08-L01 KA08-S01 合计
1 BJSISDHZ 50100005 Pax catering F-Total 140662.5 140662.5 140662.5 140662.5 140662.5 140662.5 140662.5 140662.5 74633.3 74633.3 74633.3 74633.3 74633.3 74633.3 74633.3 74633.3 0 0 1722366.4
2 BJSISDHZ 50100005 Pax catering B-Total 238470.45 238470.45 238470.45 238470.45 238470.45 238470.45 238470.45 238470.45 131850.15 131850.15 131850.15 131850.15 131850.15 131850.15 131850.15 131850.15 0 0 2962564.8
3 BJSISDHZ 50100005 Pax catering E-Total 420893.53 420893.53 420893.53 420893.53 420893.53 420893.53 420893.53 420893.53 219338.31 219338.31 219338.31 219338.31 219338.31 219338.31 219338.31 219338.31 0 0 5121854.72
4 BJSISDHZ 50100505 Complimentary  Ice-Total 38512.5 38512.5 38512.5 38512.5 38512.5 38512.5 38512.5 38512.5 20721.7 20721.7 20721.7 20721.7 20721.7 20721.7 20721.7 20721.7 0 0 473873.6
5 BJSISDHZ 50100505 Complimentary  Dry-Total 38512.5 38512.5 38512.5 38512.5 38512.5 38512.5 38512.5 38512.5 20721.7 20721.7 20721.7 20721.7 20721.7 20721.7 20721.7 20721.7 0 0 473873.6
6 BJSISDHZ 50100010 Handling Charge CWKAFW-A330 170762 170762 170762 170762 170762 170762 170762 170762 92211.48 92211.48 92211.48 92211.48 92211.48 92211.48 92211.48 92211.48 0 0 2103787.84
7 BJSISDHZ 50100010 Handling Charge CWKAFW-A321 16888.2 16888.2 16888.2 16888.2 16888.2 16888.2 16888.2 16888.2 6031.5 6031.5 6031.5 6031.5 6031.5 6031.5 6031.5 6031.5 0 0 183357.6
8 BJSISDHZ 50202501 Cabin crew Crew-total (apportionment) 7410.45 7410.45 7410.45 7410.45 7410.45 7410.45 7410.45 7410.45 3049.7 3049.7 3049.7 3049.7 3049.7 3049.7 3049.7 3049.7 0 0 83681.2
9 BJSISDHZ 50301001 Cockpit crew  Crew-total (apportionment) 35723.25 35723.25 35723.25 35723.25 35723.25 35723.25 35723.25 35723.25 20976.3 20976.3 20976.3 20976.3 20976.3 20976.3 20976.3 20976.3 0 0 453596.4
10 BJSISDHZ 50100010 Storage Storage charge 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2772.5 2772.5
11 BJSISDHZ 50100015 Lanndry Laundry charge 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 41869.25 0 41869.25
12 BJSISDHZ 50102005 Newspaper uplift   39898.5 39898.5 39898.5 39898.5 39898.5 39898.5 39898.5 39898.5 21513.7 21513.7 21513.7 21513.7 21513.7 21513.7 21513.7 21513.7 0 0 491297.6
13 合计 1147733.88 1147733.88 1147733.88 1147733.88 1147733.88 1147733.88 1147733.88 1147733.88 611047.84 611047.84 611047.84 611047.84 611047.84 611047.84 611047.84 611047.84 41869.25 2772.5 14114895.51

Answer 5

一屏装不下，只要总计就行。Union那种方式不太好实现，它是拼的一个字符串

Answer 6

这是我刚写的一段SQL，统计各地方24小时的销量，并做各地区汇总SELECT tcode_name('A007',a.br_area_gb) 地区,decode(grouping(to_char(order_date,'hh24')),1,'合计',to_char(order_date,'hh24')) 时段,
                           sum(a.order_qty) 总订购数量, sum(a.order_amt - a.dcamt) 总订购金额,
                          sum( DECODE (a.syscancel,
                                   a.order_qty, 0,
                                   a.syslast
                                  )) 有效订购数量,
                           sum(DECODE (a.syscancel,
                                   a.order_qty, 0,
                                   a.syslast_amt
                                  )) 有效订购金额,
                           sum(DECODE (a.syscancel, 0, 0, a.syscancel)) 取消数量,
                           sum(DECODE (a.syscancel,
                                   0, 0,
                                   a.order_amt - a.dcamt - a.syslast_amt
                                  )) 取消金额
                          
                      FROM 
                           torderdetail a
                     WHERE 
                        '30' <> a.item_gb
                       --and br_area_gb='85'
                       AND a.msale_gb in ('01','04')
                      and  a.order_date BETWEEN TO_DATE ('01-06-2008',
                                                         'DD-MM-YYYY'
                                                        )
                                            AND   TO_DATE ('30-06-2008',
                                                           'DD-MM-YYYY'
                                                          )
                                                + 0.99999
                      
          GROUP BY a.br_area_gb,rollup(TO_CHAR (a.order_date, 'hh24'))
ORDER BY 1,2
结果
Row# 地区 时段 总订购数量 总订购金额 有效订购数量 有效订购金额 取消数量 取消金额1 常熟 00
2 常熟 01
3 常熟 04
4 常熟 05
5 常熟 06
6 常熟 07
7 常熟 08
8 常熟 09
9 常熟 10
10 常熟 11
11 常熟 12
12 常熟 13
13 常熟 14
14 常熟 15
15 常熟 16
16 常熟 17
17 常熟 18
18 常熟 19
19 常熟 20
20 常熟 21
21 常熟 22
22 常熟 23
23 常熟 合计 业务数据我就删除了，最后结果就是这样的

Answer 7

group by rollup(列1),列2 肯定是没有问题的
多列的话可以考虑单独union，或者先通过上面单列的方式建视图，然后再关联获得其他的列

Answer 8

用这句最简单：
　　group by rollup((列1, 列2, 列3, ...))注意有两重括号哦！