从 Excel 到 SQL 实现 SUMIF() 函数
最近，我一直在学习如何使用SQL来处理数据。通常，我会使用 Python 来实现此目的，但这些类需要 SQL，而且我仍然很难在更复杂的场景中舒适地使用它。
我想要实现的结果与 Excel 中的以下屏幕截图相同：
我在 Excel 中使用的公式：
=SUMIF(B$2:B2;B2;C$2:C2)
表示例：
> select * from orders limit 5;
+------------+---------------+---------+
| ID | clientID | tonnage |
+------------+---------------+---------+
| 2005-01-01 | 872-13-44-365 | 10 |
| 2005-01-04 | 369-43-03-176 | 2 |
| 2005-01-05 | 408-24-90-350 | 2 |
| 2005-01-10 | 944-16-93-033 | 5 |
| 2005-01-11 | 645-32-78-780 | 14 |
+------------+---------------+---------+
实现应该返回与以下 group by 查询类似的结果：
select
orders.clientID as ID,
sum(orders.tonnage) as Tonnage
from orders
group by orders.clientID;
也就是说，返回每个客户端有多少已购买，但同时我想要它将添加的每个步骤作为单独的记录返回。
例如：
客户 A 在第一个订单中购买了 350 个，然后在第二个订单中购买了 231 个。
在这种情况下，查询将返回如下内容：
client A - 350 - 350 // first order
client A - 281 - 581 // second order
我已经尝试过使用类似的东西：
select
orders.clientID as ID,
sum(case when orders.clientID = <ID> then orders.tonnage end)
from orders;
但很快就卡住了，因为我需要以某种方式动态地更改它
Lately, I have been learning how to use SQL in order to process data. Normally, I would use Python for that purpose, but SQL is required for the classes and I still very much struggle with using it comfortably in more complicated scenarios.
What I want to achieve is the same result as in the following screenshot in Excel:
Behaviour in Excel, that I want to implement in SQL
The formula I used in Excel:
=SUMIF(B$2:B2;B2;C$2:C2)
Sample of the table:
> select * from orders limit 5;
+------------+---------------+---------+
| ID | clientID | tonnage |
+------------+---------------+---------+
| 2005-01-01 | 872-13-44-365 | 10 |
| 2005-01-04 | 369-43-03-176 | 2 |
| 2005-01-05 | 408-24-90-350 | 2 |
| 2005-01-10 | 944-16-93-033 | 5 |
| 2005-01-11 | 645-32-78-780 | 14 |
+------------+---------------+---------+
The implementation is supposed to return similar results as following group by query:
select
orders.clientID as ID,
sum(orders.tonnage) as Tonnage
from orders
group by orders.clientID;
That is, return how much each client have purchased, but at the same I want it to return each step of the addition as separate record.
For an instance:
Client A bought 350 in the first order and then 231 in the second one.
In such case the query would return something like this:
client A - 350 - 350 // first order
client A - 281 - 581 // second order
Example, how it would look like in Excel
I have already tried to use something like:
select
orders.clientID as ID,
sum(case when orders.clientID = <ID> then orders.tonnage end)
from orders;
But got stuck quickly, since I would need to somehow dynamically change this <ID> and store it's value in some kind of temporary variable and I can't really figure out how to implement such thing in SQL.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可以使用窗口函数来运行总和。
在您的情况下，请像这样使用
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=13a8c2d46b5ac22c5c120ac937bd6e7a
You can use window function for running sum.
In your case, use like this
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=13a8c2d46b5ac22c5c120ac937bd6e7a