1) 放弃分数>12 2)计算累积总和3)计算出所需范围内的最大累积总和4)选择累积总和为<最大累积金额
DROP table if exists t;
create table t
( Name varchar(10), score int);
insert into t values
('Anton' , 12),
('George', 12),
('John' , 1),
('Piter' , 1),
('Tom' , 2),
('bilal' , 5)
;
with
cte as
(select t.*,
sum(score) over (order by name) cumsum
from t
where score < 12) ,
cte1 as
(select max(cumsum) maxcumsum from cte where cumsum < 12 and cumsum > 5),
cte2 as
(select cte.*,cte1.maxcumsum
from cte
cross join cte1)
select * from cte2
where maxcumsum > 5 and cumsum <= maxcumsum
order by name;
+-------+-------+--------+-----------+
| Name | score | cumsum | maxcumsum |
+-------+-------+--------+-----------+
| bilal | 5 | 5 | 9 |
| John | 1 | 6 | 9 |
| Piter | 1 | 7 | 9 |
| Tom | 2 | 9 | 9 |
+-------+-------+--------+-----------+
4 rows in set (0.022 sec)