The Oracle Database row_number () over collects statistics on the first 15 enterprises, wm_concat (case when then) Row-to-column conversion, and oraclewmconcat

The Oracle Database row_number () over collects statistics on the first 15 enterprises, wm_concat (case when then) Row-to-column conversion, and oraclewmconcat

1.

Basic usage syntax of the ROW_NUMBER () OVER function: ROW_NUMBER () OVER (partition by column order by column)
For example, row_number () OVER (partition by COL1 order by COL2) indicates grouping BY COL1 and sorting BY COL2 within the group, the value calculated by this function indicates the sequential number after each group is sorted (the continuous and unique in the group)
For example, the ranking of all enterprises is queried under different product categories and measurement units.

SELECT product_type product category, prickle Metering Unit, production_name enterprise 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

Reference: http://www.cnblogs.com/fxgachiever/archive/2010/09/15/1826792.html

2. In the case when then column, wm_concat (case when then) can be used to find the metering units with different product categories and calculate the Reports of the top 15 enterprises.

The result is:

Select product_type product category, prickle Metering Unit, wm_concat (case when r = 1 then production_name end) First, wm_concat (case when r = 1 then value1 end) reporting volume, wm_concat (case when r = 2 then production_name end) second name, wm_concat (case when r = 2 then value1 end) Declaration volume, wm_concat (case when r = 3 then production_name end) third place, wm_concat (case when r = 3 then value1 end) reporting volume, wm_concat (case when r = 4 then production_name End) fourth place, wm_concat (case when r = 4 then value1 end) Declaration volume, wm_concat (case when r = 5 then production_name end) fifth place, 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) 7, wm_concat (case when r = 7 then value1 end), wm_concat (case when r = 8 Then production_name end) Eighth name, wm_concat (case when r = 8 then value1 end) Declaration volume, wm_concat (case when r = 9 then production_name end) Ninth name, 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) 11th, wm_concat (case when r = 11 then value1 end) Declaration, w M_concat (case when r = 12 then production_name end) 12th, wm_concat (case when r = 12 then value1 end), wm_concat (case when r = 13 then production_name end) 13th, wm_concat (case when r = 13 then value1 end), wm_concat (case when r = 14 then production_name end) 14th, wm_concat (case when r = 14 then value1 end) reporting volume, wm_concat (case when r = 15 then production_name end) 15th, wm_concat (case whe N r = 15 then value1 end) returns 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), '2017. 99 ') value1 FROM t_purchase_info pur, t_sgproject_info pro WHERE 1 = 1 AND pro. id = pur. pro Ject_id AND (pro. gclb = 'House construction project' OR pro. gclb is null) AND pro. status! = 9 AND product_regdate> = to_date ('1970-01-01 ', 'yyyy-mm-dd hh24: mi: ss ') AND product_regdate <= to_date ('2017-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