Question

考虑到数字:2111.03

T1数据:

Name	No
AAA	2111.03

T2 data:

Name	Start Range	End Range
AAA	0	100
AAA	100	130
AAA	130	155
AAA	155	99990

<>Expected> 数量应当逐个范围,按最大范围细分,需要显示最后的剩余数字。

Name	No
AAA	100
AAA	30
AAA	55
AAA	1926.03

请帮助我写一个问询单。

请帮助我写一下Kall PLSQL的询问。

Answer 1

I made some assumptions based on your question, as it wasn t clear. If you need adjustment, let me know. The following uses Snowflake s SQL and I added a BBB value in the data to show values that fall short of the ranges, just in case:

create or replace temp table tab1 (name string, no float) as
select name, no
from (values ( AAA ,2111.03),( BBB ,112) x (name,no));

create or replace temp table tab2 (name string, start_range number, end_range number) as
select name, start_range, end_range
from (values ( AAA ,0,100),( AAA ,100,130),( AAA ,130,155),( AAA ,155,99990),( BBB ,0,100),( BBB ,100,130),( BBB ,130,155),( BBB ,155,99990) x (name, start_range, end_range));

解决办法:

select t1.name
     , t1.no
     , sum(t2.end_range) over (partition by t2.name order by start_range rows between UNBOUNDED PRECEDING and CURRENT ROW) as max_sum
     , lag(t2.end_range) over (partition by t2.name order by start_range) as max_last
     , case when no >= max_sum then end_range-start_range
            else  
                case when no-max_last > 0 then no-max_last
                     else 0
                     end
            end as new_no
from tab1 t1
join tab2 t2
  on t1.name = t2.name;

Answer 2

I am assume you have transposed the 2,5 of the example data.

a. 利用CTE数据:

with t1(name, no) as (
    select * from values 
        ( AAA ,2111.03)
), t2(name, start_range, end_range) as (
    select * from values
        ( AAA , 0, 100),
        ( AAA , 100, 130),
        ( AAA , 130, 155),
        ( AAA , 155, 9999)
)

该表包括:

select t1.name
    ,iff(t1.no < t2.end_range, greatest(0, no - t2.start_range), t2.end_range-t2.start_range) as no
from t1
join t2 
    on t1.name = t2.name
order by 1, start_range;

友情链接