SQL练习题一(逐行累计)

SQL练习题一(逐行累计)

---

表table1如下，ID为自增，查询出第一条开始到第几条记录的累计金额刚好超过100？

下面是创建表语句：

CREATE TABLE table1 (
ID INT,
金额 INT
)

INSERT INTO table1 VALUES (1,30);
INSERT INTO table1 VALUES (2,30);
INSERT INTO table1 VALUES (4,30);
INSERT INTO table1 VALUES (8,9);
INSERT INTO table1 VALUES (11,1);
INSERT INTO table1 VALUES (12,1);
INSERT INTO table1 VALUES (14,15);
INSERT INTO table1 VALUES (15,33);
INSERT INTO table1 VALUES (16,5);
INSERT INTO table1 VALUES (17,8);
INSERT INTO table1 VALUES (18,14);
INSERT INTO table1 VALUES (19,3);

---

我这里用的方法是先进行逐行累计，再进行对比找出目标ID

1.用表链接实现

select min(id) from 
(select 
a.id
,a.金额
,sum(b.金额) 累计金额
from table1 a,table1 b
where a.id>=b.id
group by a.id,a.金额 having sum(b.金额)>100
order by a.id) a

2.用开窗函数实现

select 
id
from 
(select 
id
,金额
,sum(金额) over(order by id) 累计金额
from table1
group by id,金额) a 
where 累计金额>100
order by id limit 1

---

总结:如果有别的方法的小伙伴，欢迎一起来分享一下！