在我的数据库中，特定产品的计划销售被保存为一年中每个月。对于其他产品，计划将保存为一年中的每一周。 我必须将这两个计划统一到几周。对于基于月度的计划，我需要将其分成几周。我正在尝试将每月数据拆分为数周 在特定月份使用周数并计算周值，但我不知道如何获得周表。 所以使用SQL如何转换这个表
select
month,
value,
week_count,
round(value/week_count,0) weekly_value
from
my_table
-=======T=======T=============T===============¬
¦ MONTH ¦ VALUE ¦ WEEK_COUNT ¦ WEEKLY_VALUE ¦
¦=======+=======+=============+===============¦
¦ 1 ¦ 20 ¦ 4 ¦ 5 ¦
¦ 2 ¦ 28 ¦ 4 ¦ 7 ¦
¦ 3 ¦ 35 ¦ 5 ¦ 7 ¦
¦ 4 ¦ 8 ¦ 4 ¦ 2 ¦
¦ 5 ¦ 15 ¦ 5 ¦ 3 ¦
¦ 6 ¦ 24 ¦ 4 ¦ 6 ¦
¦ 7 ¦ 24 ¦ 4 ¦ 6 ¦
¦ 8 ¦ 20 ¦ 5 ¦ 4 ¦
¦ 9 ¦ 40 ¦ 4 ¦ 10 ¦
¦ 10 ¦ 48 ¦ 4 ¦ 12 ¦
¦ 11 ¦ 45 ¦ 5 ¦ 9 ¦
¦ 12 ¦ 36 ¦ 4 ¦ 9 ¦
L=======¦=======¦=============¦===============-
进入此表：
-=======T=======¬
¦ WEEK ¦ VALUE ¦
¦=======+=======¦
¦ 1 ¦ 5 ¦
¦ 2 ¦ 5 ¦
¦ 3 ¦ 5 ¦
¦ 4 ¦ 5 ¦
¦ 5 ¦ 7 ¦
¦ 6 ¦ 7 ¦
¦ 7 ¦ 7 ¦
¦ 8 ¦ 7 ¦
¦ 9 ¦ 7 ¦
...
¦ 43 ¦ 12 ¦
¦ 44 ¦ 9 ¦
¦ 45 ¦ 9 ¦
¦ 46 ¦ 9 ¦
¦ 47 ¦ 9 ¦
¦ 48 ¦ 9 ¦
¦ 49 ¦ 9 ¦
¦ 50 ¦ 9 ¦
¦ 51 ¦ 9 ¦
¦ 52 ¦ 9 ¦
L=======¦=======-
感谢您的任何建议。
这是一个选项;看看它是否适合您的需求。
SQL> with my_table (month, week_count, weekly_value) as
2 (
3 select 1, 4, 5 from dual union all
4 select 2, 4, 7 from dual union all
5 select 3, 5, 7 from dual union all
6 select 4, 4, 2 from dual union all
7 select 5, 5, 3 from dual union all
8 select 6, 4, 6 from dual union all
9 select 7, 4, 6 from dual union all
10 select 8, 5, 4 from dual union all
11 select 9, 4, 10 from dual union all
12 select 10, 4, 12 from dual union all
13 select 11, 5, 9 from dual union all
14 select 12, 4, 9 from dual
15 )
16 select row_number() over (order by t.month) week,
17 t.weekly_value
18 from my_table t,
19 table(cast(multiset(select level from dual
20 connect by level <= t.week_count
21 ) as sys.odcinumberlist ))
22 order by 1;
WEEK WEEKLY_VALUE
---------- ------------
1 5
2 5
3 5
4 5
5 7
6 7
7 7
8 7
9 7
10 7
11 7
12 7
13 7
14 2
15 2
16 2
17 2
18 3
19 3
20 3
21 3
22 3
23 6
24 6
25 6
26 6
27 6
28 6
29 6
30 6
31 4
32 4
33 4
34 4
35 4
36 10
37 10
38 10
39 10
40 12
41 12
42 12
43 12
44 9
45 9
46 9
47 9
48 9
49 9
50 9
51 9
52 9
52 rows selected.
SQL>