国内最全IT社区平台 联系我们 | 收藏本站
华晨云阿里云优惠2
您当前位置:首页 > 数据库 > 数据库应用 > Oracle数据库row_number() over统计前15名企业, wm_concat(case when then)行转列

Oracle数据库row_number() over统计前15名企业, wm_concat(case when then)行转列

来源:程序员人生   发布时间:2015-03-07 13:02:14 阅读次数:4212次

1.

ROW_NUMBER() OVER函数的基本用法

语法:ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
例如:row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯1的)
如:不同的产品种别和计量单位下,查询所有企业的排名。
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) 第1名, wm_concat(case when r = 1 then value1 end) 申报量, wm_concat(case when r = 2 then production_name end) 第2名, wm_concat(case when r = 2 then value1 end) 申报量, wm_concat(case when r = 3 then production_name end) 第3名, wm_concat(case when r = 3 then value1 end) 申报量, wm_concat(case when r = 4 then production_name end) 第4名, wm_concat(case when r = 4 then value1 end) 申报量, wm_concat(case when r = 5 then production_name end) 第5名, wm_concat(case when r = 5 then value1 end) 申报量, wm_concat(case when r = 6 then production_name end) 第6名, wm_concat(case when r = 6 then value1 end) 申报量, wm_concat(case when r = 7 then production_name end) 第7名, wm_concat(case when r = 7 then value1 end) 申报量, wm_concat(case when r = 8 then production_name end) 第8名, wm_concat(case when r = 8 then value1 end) 申报量, wm_concat(case when r = 9 then production_name end) 第9名, wm_concat(case when r = 9 then value1 end) 申报量, wm_concat(case when r = 10 then production_name end) 第10名, wm_concat(case when r = 10 then value1 end) 申报量, wm_concat(case when r = 11 then production_name end) 第101名, wm_concat(case when r = 11 then value1 end) 申报量, wm_concat(case when r = 12 then production_name end) 第102名, wm_concat(case when r = 12 then value1 end) 申报量, wm_concat(case when r = 13 then production_name end) 第103名, wm_concat(case when r = 13 then value1 end) 申报量, wm_concat(case when r = 14 then production_name end) 第104名, wm_concat(case when r = 14 then value1 end) 申报量, wm_concat(case when r = 15 then production_name end) 第105名, 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⑴2⑶1 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







生活不易,码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
程序员人生
------分隔线----------------------------
分享到:
------分隔线----------------------------
关闭
程序员人生