Oracle數據庫row_number()over統計前15名企業，wm_concat(casewhenthen)行轉列

Oracle數據庫row_number()over統計前15名企業，wm_concat(casewhenthen)行轉列

ROW_NUMBER() OVER函數的基本用法

語法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
例如：row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根據COL1分組，在分組內部根據 COL2排序，而此函數計算的值就表示每組內部排序後的順序編號（組內連續的唯一的)
如：不同的產品類別和計量單位下，查詢所有企業的排名。

SELECT product_type 產品類別,
       prickle 計量單位,
       production_name 企業名稱,
       row_number() over(partition by product_type, prickle order by sum(pur.purchase_num) desc)   名次
  from t_purchase_info pur
 group by  production_name, product_type, prickle

2.列轉行 wm_concat(case when then) 查出來不同的產品類別不同的計量單位下，統計出前15名企業的申報量

查出來的效果是：

 

 

select product_type 產品類別,
       prickle 計量單位,
       wm_concat(case
                   when r = 1 then
                    production_name
                 end) 第一名,
       wm_concat(case
                   when r = 1 then
                    value1
                 end) 申報量,
       wm_concat(case
                   when r = 2 then
                    production_name
                 end) 第二名,
       wm_concat(case
                   when r = 2 then
                    value1
                 end) 申報量,
       wm_concat(case
                   when r = 3 then
                    production_name
                 end) 第三名,
       wm_concat(case
                   when r = 3 then
                    value1
                 end) 申報量,
       wm_concat(case
                   when r = 4 then
                    production_name
                 end) 第四名,
       wm_concat(case
                   when r = 4 then
                    value1
                 end) 申報量,
       wm_concat(case
                   when r = 5 then
                    production_name
                 end) 第五名,
       wm_concat(case
                   when r = 5 then
                    value1
                 end) 申報量,
       wm_concat(case
                   when r = 6 then
                    production_name
                 end) 第六名,
       wm_concat(case
                   when r = 6 then
                    value1
                 end) 申報量,
       wm_concat(case
                   when r = 7 then
                    production_name
                 end) 第七名,
       wm_concat(case
                   when r = 7 then
                    value1
                 end) 申報量,
       wm_concat(case
                   when r = 8 then
                    production_name
                 end) 第八名,
       wm_concat(case
                   when r = 8 then
                    value1
                 end) 申報量,
       wm_concat(case
                   when r = 9 then
                    production_name
                 end) 第九名,
       wm_concat(case
                   when r = 9 then
                    value1
                 end) 申報量,
       wm_concat(case
                   when r = 10 then
                    production_name
                 end) 第十名,
       wm_concat(case
                   when r = 10 then
                    value1
                 end) 申報量,
       wm_concat(case
                   when r = 11 then
                    production_name
                 end) 第十一名,
       wm_concat(case
                   when r = 11 then
                    value1
                 end) 申報量,
       wm_concat(case
                   when r = 12 then
                    production_name
                 end) 第十二名,
       wm_concat(case
                   when r = 12 then
                    value1
                 end) 申報量,
       wm_concat(case
                   when r = 13 then
                    production_name
                 end) 第十三名,
       wm_concat(case
                   when r = 13 then
                    value1
                 end) 申報量,
       wm_concat(case
                   when r = 14 then
                    production_name
                 end) 第十四名,
       wm_concat(case
                   when r = 14 then
                    value1
                 end) 申報量,
       wm_concat(case
                   when r = 15 then
                    production_name
                 end) 第十五名,
       wm_concat(case
                   when r = 15 then
                    value1
                 end) 申報量

  from (SELECT r, production_name, product_type, prickle, value1
          FROM (SELECT row_number() over(partition by product_type, prickle order by sum(pur.purchase_num) desc) r,
                       pur.production_name,
                       pur.product_type,
                       prickle,
                       to_char(round(sum(pur.purchase_num), 2),
                               '9999999999999999999.99') value1
                  FROM t_purchase_info pur, t_sgproject_info pro
                 WHERE 1 = 1
                   AND pro.id = pur.project_id
                   AND (pro.gclb = '房屋建築工程' OR pro.gclb IS NULL)
                   AND pro.status != 9
                   AND product_regdate >=
                       to_date('2014-01-01', 'yyyy-mm-dd hh24:mi:ss')
                   AND product_regdate <=
                       to_date('2014-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
                   AND sgproject_type = 1
                 GROUP BY production_name, product_type, prickle)
         WHERE r <= 15
         ORDER BY product_type, prickle, r)
 group by product_type, prickle