连接两个表并对两个表中的列求和

发布于 2025-01-15 13:55:10 字数 664 浏览 1 评论 0原文

使用MySQL来存储和查询一些数据。我有两个表表

A（成本列表）： id, order_id, amount, rates

表B（最终订单列表）： id、order_id、total、rate

两个表中的费率均为百分比。对于每个最终订单行，A 中都有许多行成本。我正在寻找的行为是输出 B 中所有行的利润总和，其中包括 A 的成本。

假设 A 的行如下：

1, 69, 420, 15
2, 69, 100, 20

B 的行code>:

1, 69, 1000, 10
2, 70, 500, 30

数学看起来像是

((1000 - (420 * 15 / 100) - (100 * 20 / 100)) * 10 / 100)
+
(500 * 30 / 100)

= 241.7

我可以通过子查询来完成这个任务，但是我担心 B 中的行负载不会很快，每个行在 A 中都有 0-30 行关联，而且它会进行是一个经常发生的查询。

感谢任何帮助，如果有什么需要澄清，请告诉我！ :)

原文

Using MySQL to store and query some data.
I have two tables

Table A (list of costs):
id, order_id, amount, rate

Table B (list of finalized orders):
id, order_id, total, rate

Rate in both tables are percents.
For every finalized order row, there are many rows of costs in A.
The behavior I'm looking for is to output the sum of profit of all rows in B which include the costs of A.

Assuming the following rows for A:

1, 69, 420, 15
2, 69, 100, 20

And the rows for B:

1, 69, 1000, 10
2, 70, 500, 30

The math would look something like

((1000 - (420 * 15 / 100) - (100 * 20 / 100)) * 10 / 100)
+
(500 * 30 / 100)

= 241.7

I can probably get this done with subqueries, but I'm afraid it won't be very fast with loads of rows in B that each have 0-30 rows in A associated, and it's going to be a query that happens often.

Any help is appreciated and if something needs clarification let me know ! :)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

倒带 2025-01-22 13:55:10

使用 2 级聚合：

SELECT SUM((b.total - COALESCE(a.amount, 0)) * b.rate) / 100 total_profit
FROM tableB b 
LEFT JOIN (
  SELECT order_id, SUM(amount * rate) / 100 amount
  FROM tableA
  GROUP BY order_id
) a ON a.order_id = b.order_id;

请参阅演示。

Use 2 levels of aggregation:

SELECT SUM((b.total - COALESCE(a.amount, 0)) * b.rate) / 100 total_profit
FROM tableB b 
LEFT JOIN (
  SELECT order_id, SUM(amount * rate) / 100 amount
  FROM tableA
  GROUP BY order_id
) a ON a.order_id = b.order_id;

See the demo.

回复收藏 0 原文

~没有更多了~