使用分组方式时如何在联接表中获得总和-获取错误的结果 [英] How to get the sum in a joined table when using group by - getting wrong results
问题描述
我有两个表 orders
和 order_items
。我需要按天分组结果。但是我还需要每天从另一个表中获取 energy_used
的总和。当我尝试使用联接进行操作时,每天都会出错 order_sum
。不确定我在做什么错。
I have two tables orders
and order_items
. I need to group the results by days. But I also need to get the sum of energy_used
for each day from another table. When I try that using a join, I get wrong order_sum
for each day. Not sure what I am doing wrong.
我使用联接是因为稍后我也想按这些列进行排序。
I am using joins because I would also like to sort by these columns later.
这是我的订单
表
+----+-----------+---------+---------------------+
| id | order_sum | user_id | created_at |
+----+-----------+---------+---------------------+
| 1 | 25.13 | 7 | 2020-01-25 09:13:00 |
| 2 | 14.00 | 5 | 2020-01-26 10:14:00 |
| 3 | 35.00 | 1 | 2020-01-27 11:13:00 |
+----+-----------+---------+---------------------+
这是我的 order_items
表格
+----+----------+-------------+---------------------+
| id | order_id | energy_used | created_at |
+----+----------+-------------+---------------------+
| 1 | 1 | 65 | 2020-01-25 09:13:00 |
| 2 | 1 | 12 | 2020-01-25 09:13:00 |
| 3 | 2 | 70 | 2020-01-26 10:14:00 |
| 4 | 2 | 5 | 2020-01-26 10:14:00 |
| 5 | 3 | 0 | 2020-01-27 11:13:00 |
+----+----------+-------------+---------------------+
这是我想要实现的理想结果
And this is the desired result that I am trying to achieve
+---------------+-----------------+-------------------+---------------------+----------------+
| date_of_month | total_order_sum | total_energy_used | last_order_date | last_order_sum |
+---------------+-----------------+-------------------+---------------------+----------------+
| 2020-01-25 | 25.13 | 77 | 2020-01-25 09:13:00 | 25.13 |
| 2020-01-26 | 14.00 | 75 | 2020-01-26 10:14:00 | 14.00 |
| 2020-01-27 | 35.00 | 0 | 2020-01-27 11:13:00 | 35.00 |
+---------------+-----------------+-------------------+---------------------+----------------+
这是我尝试过的查询,但结果不正确,order_sum未被计算正确,因为我加入了order_items表
And here is my query that I have tried but I'm getting wrong results, the order_sum is not being calculated correctly because of my join with the order_items table
SELECT
DATE(orders.created_at) AS date_of_month,
SUM(orders.order_sum) AS order_sum,
order_items.energy_used
FROM
orders
JOIN (
select
order_id,
sum(energy_used) as energy_used
from
order_items
group by
date(created_at)
) as order_items on order_items.order_id = orders.id
JOIN (
select
max(created_at) as last_order_date,
max(order_sum) as last_order_sum
from
orders
order by created at desc
limit 1
) as orders2 on orders2.id = orders.id
GROUP BY
date_of_month
推荐答案
我知道您每天都想要:
I understand that you want, for each day:
- 当天创建的所有订单的
order_items.energy_used
之和 - 与最新的
订单相对应的
当天创建的created_at
和order_sum
- the sum of
order_items.energy_used
for all orders created that day - the
created_at
andorder_sum
that correspond to the latestorder
created on that day
您的样本数据并不能很好地代表这一点-每个订单只有一个订单天。同样也不清楚为什么在两个表中都重复 created_at
;您确实应该依靠 order_id
关联两个表
Your sample data is not very representative of that - it has only one order per day. Also it is unclear why created_at
is repeated in both tables; you should really be relying on the order_id
to relate both tables
这里是用于上述目的的查询:通过将 orders
表与用于计算每天总能量的汇总查询(使用 order_id
来获取 created_at
日期(来自订单
),并过滤每天的最新订单:
Here is a query for the above purpose: this works by joining the orders
table with an aggregate query that computes the total energy per day (using order_id
to get the created_at
date from orders
), and filters on the latest order per day:
select
date(o.created_at) date_of_month,
i.total_energy_used,
o.created_at last_order_date,
o.order_sum last_order_sum
from orders o
inner join (
select date(o1.created_at) date_of_month, sum(i1.energy_used) total_energy_used
from orders o1
inner join order_items i1 on o1.id = i1.order_id
group by date(o1.created_at)
) i on i.date_of_month = date(o.created_at)
where o.created_at = (
select max(o1.created_at)
from orders o1
where date(o1.created_at) = date(o.created_at)
)
Demo on DB Fiddle:
date_of_month | total_energy_used | last_order_date | last_order_sum
:------------ | ----------------: | :------------------ | -------------:
2020-01-25 | 77 | 2020-01-25 09:13:00 | 25.13
2020-01-26 | 75 | 2020-01-26 10:14:00 | 14.00
2020-01-27 | 0 | 2020-01-27 11:13:00 | 35.00
这篇关于使用分组方式时如何在联接表中获得总和-获取错误的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!