这很简单：
DBFiddle：https://dbfiddle.uk/ovlq7ZjO

with t as
 (select 1  from_hour,4  to_hour,20 pay from dual union all
  select 5  from_hour,8  to_hour,50 pay from dual union all
  select 10  from_hour,12  to_hour,100 pay from dual)
, input_data
as
(
select 6 user_hours from dual
)
select *
  from input_data i
  outer apply(
     select 
        sum((least(t.to_hour,i.user_hours) - (t.from_hour-1) ) * t.pay) as sum_to_pay
     from t
     where i.user_hours>= t.from_hour
  );

或者解释一下：

with t as
 (select 1  from_hour,4  to_hour,20 pay from dual union all
  select 5  from_hour,8  to_hour,50 pay from dual union all
  select 10  from_hour,12  to_hour,100 pay from dual)
, input_data
as
(
select 6 user_hours from dual
)
select *
  from input_data i
  outer apply(
     select 
        (least(t.to_hour,i.user_hours) - (t.from_hour-1) ) as hours_to_pay
       ,(least(t.to_hour,i.user_hours) - (t.from_hour-1) ) * t.pay as sum_to_pay
       ,t.*
     from t
     where i.user_hours>= t.from_hour
  )
  ;

hours_to_pay-是一个小时数，我们通过比较user_hours和to_hour得到
使用outer apply和sum()来聚合结果

看起来你的“total_pay”列是从“hours_worked”列派生出来的，所以我们：

• 首先使用带条件的CASE表达式计算工作时间
• 然后我们应用“hours_worked”和相应的“pay”之间的乘积

WITH t AS (
  SELECT  1 from_hour,  4 to_hour,  20 pay FROM dual UNION ALL
  SELECT  5 from_hour,  8 to_hour,  50 pay FROM dual UNION ALL
  SELECT  9 from_hour, 12 to_hour, 100 pay FROM dual
), input_data AS (
  SELECT 6 user_hours FROM dual
)
SELECT t.from_hour,
       t.to_hour,
       t.pay,
       CASE WHEN t.to_hour <= i.user_hours THEN 4 
            ELSE i.user_hours - t.from_hour +1 
       END AS num_hours,
       t.pay * CASE WHEN t.to_hour <= i.user_hours THEN 4 
                    ELSE i.user_hours - t.from_hour +1 
               END AS total_pay
FROM       t
INNER JOIN input_data i
        ON t.from_hour <= i.user_hours

输出：

| 开始_小时|至小时|支付|小时数|总计_支付|
| - -----|- -----|- -----|- -----|- -----|
| 一个|4|二十个|4|八十|
| 5个|八|五十|2|一百|
查看演示here。

您可以使用相关子查询来查找工资总额：

select i.user_hours,
       ( SELECT SUM( (LEAST(i.user_hours, to_hour) - from_hour + 1) * pay )
         FROM   t
         WHERE  from_hour <= i.user_hours
       ) AS total_pay
from   input_data i;

其中，对于样本数据：

CREATE TABLE t (from_hour, to_hour, pay) AS
  select  1,  4,  20 from dual union all
  select  5,  8,  50 from dual union all
  select  9, 12, 100 from dual;

CREATE TABLE input_data (user_hours) AS
  select 6 user_hours from dual;

输出：
| 用户小时数|总计_支付|
| - -----|- -----|
| 六|一百八十|
fiddle