使用分组方式时如何在联接表中获得总和-获取错误的结果 [英] 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 and order_sum that correspond to the latest order 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)
)

DB Fiddle上的演示 ：

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屋！