grouping-rollup-ROW_NUMBER-over-PARTITION相关技术求助（第三贴）

Question

表TEST：  
CITY USER_TYPE  NUM  PRODUCT_TYPE  
---------------------------------------------  
PEK   A          12     X  
PEK   B          13     Y  
SHA   A          15     X  
SHA   B          22     Z  
SHA   C           4     Y  
当有多个城市时用下面的查询语句可以查出符合要求的结果，
但是当只有一个城市时，不想显示subtotal，只显示total，该怎么办？
比较着急交差，还请各位帮忙。-------------------------------------------------------------------
SELECT DECODE (rn, 1, city, NULL, city, NULL) city, product_type,
       DECODE (rn, NULL, nvl(a,0), DECODE (a, 0, NULL, a)) a,
       DECODE (rn, NULL, nvl(b,0), DECODE (b, 0, NULL, b)) b,
       DECODE (rn, NULL, nvl(c,0), DECODE (c, 0, NULL, c)) c
  FROM (SELECT   DECODE
                    (GROUPING (city),
                     1, '',
                     DECODE
                           (GROUPING (product_type),
                            1, '',
                            ROW_NUMBER () OVER (PARTITION BY city ORDER BY product_type)
                           )
                    ) rn,
                 DECODE (GROUPING (city),
                         1, '',
                         DECODE (GROUPING (product_type), 1, '', city)
                        ) city,
                 DECODE (GROUPING (city),
                         1, 'TOTAL',
                         DECODE (GROUPING (product_type),
                                 1, 'SUBTOTAL',
                                 product_type
                                )
                        ) product_type,
                 SUM (DECODE (user_type, 'A', num, '')) AS "A",
                 SUM (DECODE (user_type, 'B', num, '')) AS "B",
                 SUM (DECODE (user_type, 'C', num, '')) AS "C"
            FROM TEST
        GROUP BY ROLLUP (city, product_type))结果Row#    CITY    PRODUCT_TYPE    A    B    C
-----------------------------------------------
1        PEK       X           12        
2                  Y                 13    
3                 SUBTOTAL     12    13    0
4        SHA       X           15        
5                  Y                       4
6                  Z                 22    
7                 SUBTOTAL     15    22    4
8                  TOTAL       27    35    4
第一帖：http://topic.csdn.net/u/20080429/21/713f9c3c-f642-4b6c-9780-39cea10e87ff.html
第二贴：http://topic.csdn.net/u/20080504/15/6378b921-7c96-4d03-b42d-fb4b9c74baf9.html

Answer 1

 你可以在里面再生成个字段
 DECODE 
                    (GROUPING (city), 
                     1, 2, 
                     DECODE 
                           (GROUPING (product_type), 
                            1, count(distict city) over(), 
                            3 
                           ) 
                    ) sub_rn, 
然后在外层，加个where sub_rn>1

Answer 2

SELECT DECODE (rn, 1, city, NULL, city, NULL) city, product_type,
       DECODE (rn, NULL, NVL (a, 0), DECODE (a, 0, NULL, a)) a,
       DECODE (rn, NULL, NVL (b, 0), DECODE (b, 0, NULL, b)) b,
       DECODE (rn, NULL, NVL (c, 0), DECODE (c, 0, NULL, c)) c
  FROM (SELECT   DECODE
                    (GROUPING (city),
                     1, '',
                     DECODE
                           (GROUPING (product_type),
                            1, '',
                            ROW_NUMBER () OVER (PARTITION BY city ORDER BY product_type)
                           )
                    ) rn,
                 DECODE (GROUPING (city),
                         1, 2,
                         DECODE (GROUPING (product_type),
                                 1, COUNT (DISTINCT city) OVER (),
                                 3
                                )
                        ) sub_rn,
                 DECODE (GROUPING (city),
                         1, '',
                         DECODE (GROUPING (product_type), 1, '', city)
                        ) city,
                 DECODE (GROUPING (city),
                         1, 'TOTAL',
                         DECODE (GROUPING (product_type),
                                 1, 'SUBTOTAL',
                                 product_type
                                )
                        ) product_type,
                 SUM (DECODE (user_type, 'A', num, '')) AS "A",
                 SUM (DECODE (user_type, 'B', num, '')) AS "B",
                 SUM (DECODE (user_type, 'C', num, '')) AS "C"
            FROM TEST
        GROUP BY ROLLUP (city, product_type))
 WHERE sub_rn > 1

Answer 3

测试  WITH TEST AS
     (SELECT 'PEK' city, 'A' user_type, 12 num, 'X' product_type
        FROM DUAL
      UNION ALL
      SELECT 'PEK', 'B', 13, 'Y'
        FROM DUAL
      )
SELECT DECODE (rn, 1, city, NULL, city, NULL) city, product_type,
       DECODE (rn, NULL, NVL (a, 0), DECODE (a, 0, NULL, a)) a,
       DECODE (rn, NULL, NVL (b, 0), DECODE (b, 0, NULL, b)) b,
       DECODE (rn, NULL, NVL (c, 0), DECODE (c, 0, NULL, c)) c
  FROM (SELECT   DECODE
                    (GROUPING (city),
                     1, '',
                     DECODE
                           (GROUPING (product_type),
                            1, '',
                            ROW_NUMBER () OVER (PARTITION BY city ORDER BY product_type)
                           )
                    ) rn,
                 DECODE (GROUPING (city),
                         1, 2,
                         DECODE (GROUPING (product_type),
                                 1, COUNT (DISTINCT city) OVER (),
                                 3
                                )
                        ) sub_rn,
                 DECODE (GROUPING (city),
                         1, '',
                         DECODE (GROUPING (product_type), 1, '', city)
                        ) city,
                 DECODE (GROUPING (city),
                         1, 'TOTAL',
                         DECODE (GROUPING (product_type),
                                 1, 'SUBTOTAL',
                                 product_type
                                )
                        ) product_type,
                 SUM (DECODE (user_type, 'A', num, '')) AS "A",
                 SUM (DECODE (user_type, 'B', num, '')) AS "B",
                 SUM (DECODE (user_type, 'C', num, '')) AS "C"
            FROM TEST
        GROUP BY ROLLUP (city, product_type))
 WHERE sub_rn > 1;结果
Row# CITY PRODUCT_TYPE A B C1 PEK X 12
2 Y 13
3 TOTAL 12 13 0

Answer 4

测试数据
WITH TEST AS
     (SELECT 'PEK' city, 'A' user_type, 12 num, 'X' product_type
        FROM DUAL
      UNION ALL
      SELECT 'PEK', 'B', 13, 'Y'
        FROM DUAL
      UNION ALL
      SELECT 'SHA', 'A', 15, 'X'
        FROM DUAL
      UNION ALL
      SELECT 'SHA', 'B', 22, 'Z'
        FROM DUAL
      UNION ALL
      SELECT 'SHA', 'C', 4, 'Y'
        FROM DUAL)结果Row# CITY PRODUCT_TYPE A B C1 PEK X 12
2 Y 13
3 SUBTOTAL 12 13 0
4 SHA X 15
5 Y 4
6 Z 22
7 SUBTOTAL 15 22 4
8 TOTAL 27 35 4

Answer 5

select 
   decode(DS.City, NULL, 'TOTAL', to_char(DS.rn)) Rn, 
   decode(DS.rn, 1, DS.City, '') City, 
   DS.Product_Type, 
   DS.A, DS.B, DS.C
from 
   ( select 
        row_number() over(partition by City order by Product_Type) rn,
        City, Product_Type,         
        sum(decode(User_Type, 'A', Num, NULL)) "A",
        sum(decode(User_Type, 'B', Num, NULL)) "B",
        sum(decode(User_Type, 'C', Num, NULL)) "C"   
     from Test1
     group by rollup(City, Product_Type) ) DS
where (DS.City is not null and DS.Product_Type is not null)
or (DS.City is null and DS.Product_Type is null)

Answer 6

select 
   decode(DS.City, NULL, 'TOTAL', to_char(DS.rn)) Rn, 
   decode(DS.rn, 1, DS.City, '') City, 
   DS.Product_Type, 
   DS.A, DS.B, DS.C
from 
   ( select 
        row_number() over(partition by City order by Product_Type) rn,
        City, Product_Type,         
        sum(decode(User_Type, 'A', Num, NULL)) "A",
        sum(decode(User_Type, 'B', Num, NULL)) "B",
        sum(decode(User_Type, 'C', Num, NULL)) "C"   
     from Test
     group by rollup(City, Product_Type) ) DS
where (DS.City is not null and DS.Product_Type is not null)
or (DS.City is null and DS.Product_Type is null)

Answer 7

多谢两位
我再学习一下COUNT (DISTINCT sta_code) OVER ()这句里面的over()是不是就表示count统计的时候，不受group by的限制只统计一类的数量

Answer 8

用到over了，就是受over时面的条件限制了
一般的
over(partition by 字段1，字段2  相当于group by
order by 
range 100 preceding 100 following 需要配合order by 使用 前100条到后100条)一般常用的分析函数row_number() over
dense_rank() over
rank() over
lag
之类
像平常用的sum,avg,min,max均可以这样用
详细搜索下oracle+分析函数

Answer 9

在hebo2005的基础上加以简化后
SELECT DECODE (rn, 1, city, NULL) city, product_type,
       DECODE (sign(rn), -1, a, DECODE (a, 0, NULL, a)) a,
       DECODE (sign(rn), -1, b, DECODE (b, 0, NULL, b)) b,
       DECODE (sign(rn), -1, c, DECODE (c, 0, NULL, c)) c
  FROM (SELECT   DECODE
                    (GROUPING (city),
                     1, -0.1,
                     DECODE
                           (GROUPING (product_type),
                            1, COUNT (DISTINCT city) OVER ()*-1,
                            ROW_NUMBER () OVER (PARTITION BY city ORDER BY product_type)
                           )
                    ) rn,
                 city,
                 DECODE (GROUPING (city)+GROUPING (product_type),
                         2, 'TOTAL',
                         1, 'SUBTOTAL',
                         product_type) product_type,
                 SUM (DECODE (user_type, 'A', nvl(num,0), 0)) AS "A",
                 SUM (DECODE (user_type, 'B', nvl(num,0), 0)) AS "B",
                 SUM (DECODE (user_type, 'C', nvl(num,0), 0)) AS "C"
            FROM TEST
        GROUP BY ROLLUP (city, product_type))
  where rn<>-1;

Answer 10

多谢各位支持，问题基本解决了
bobfang 的方法也测试通过了：）