Oracle資料庫row_number() over統計前15名企業， wm_concat(case when then)行轉列，oraclewmconcat

Oracle資料庫row_number() over統計前15名企業， wm_concat(case when then)行轉列，oraclewmconcat

1.

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

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

參考：http://www.cnblogs.com/fxgachiever/archive/2010/09/15/1826792.html

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