17,377
社区成员
发帖
与我相关
我的任务
分享
SQL> select * from tSaleCount t;
PRODUCT_NAME JAN FEB MAR
-------------------- ---------- ---------- ----------
A 100 200 300
B 500 400 600
C 300 700 200
D 500 400 600
SQL> create or replace function p_rank(p1 number,p2 number,p3 number)
2 return number
3 is
4 rank number;
5 begin
6 select rn into rank
7 from
8 (
9 select quantity,row_number() over(order by quantity desc) rn
10 from
11 (
12 select p1 quantity
13 from dual
14 union all
15 select p2 quantity
16 from dual
17 union all
18 select p3 quantity
19 from dual
20 )
21 )
22 where quantity = p2;
23 return rank;
24 end;
25 /
Function created
SQL> select t.*,p_rank(jan,feb,mar) from tSaleCount t;
PRODUCT_NAME JAN FEB MAR P_RANK(JAN,FEB,MAR)
-------------------- ---------- ---------- ---------- -------------------
A 100 200 300 2
B 500 400 600 3
C 300 700 200 1
D 500 400 600 3
with temp as
select 产品名称 row_number() over(partition by 产品名称 order by sale) rn from (
select 产品名称, 2月销量 sale , '1' flag from your_table
union all
select 产品名称, 1月销量, '0' flag from your_table
union all
select 产品名称, 3月销量, '0' flag from your_table
union all
select 产品名称, 4月销量, '0' flag from your_table
union all
select 产品名称, 5月销量, '0' flag from your_table
union all
select 产品名称, 6月销量, '0' flag from your_table
union all
select 产品名称, 7月销量, '0' flag from your_table
union all
select 产品名称, 8月销量, '0' flag from your_table
union all
select 产品名称, 9月销量, '0' flag from your_table
union all
select 产品名称, 10月销量, '0' flag from your_table
union all
select 产品名称, 11月销量, '0' flag from your_table
union all
select 产品名称, 12月销量, '0' flag from your_table
)
where flag = '1'
select a.* ,b.rn 2月排名 from your_table a, temp b
where a.产品名称 = b.产品名称
在家里 没法试验 不知道这样行不行