Mysql左连接和总和不能按月和年正确地与组 [英] Mysql Left Join And Sum not Working Correctly with group by month and year

问题描述

我想在 mysql 上获得group_by和year和year的total_amount和Received_amt的总和.但是我面临一个问题.问题是第二个表的列总和与按月和年分组时不正确.

I want to get a sum of total_amount and received_amt with group by with month and year on mysql. But I am facing a problem. The problem is second table column sum not correct with group by month and year.

当前查询

SELECT t1.order_date,sum(IFNULL(t1.received_amt, 0)) as SumOfNO,
sum(IFNULL(t2.total_amount, 0)) as SumOfSM,
SUM(IFNULL(t1.received_amt, 0)  + IFNULL(t2.total_amount, 0)) AS Total
FROM `new_order` t1
LEFT JOIN
 (  select t2.sell_date,t2.total_amount, sum(total_amount) as Amount
    from sell_master t2
    group by YEAR(t2.sell_date), MONTH(t2.sell_date) 
 ) t2
 ON format(t1.order_date,'yyyy-MM') = format(t2.sell_date,'yyyy-MM')
GROUP BY YEAR(t1.order_date), MONTH(t1.order_date)
ORDER BY t1.order_date DESC

示例:

第一张桌子:new_order

First Table: new_order

第二张表:sell_master

Second Table: sell_master

表结构:

新订单

+----------------------------------------+
|order_date(date) | received_amt(double) |
+----------------------------------------+
|2007-10-06       | 245                  |
|2007-10-06       | 310                  |                             
|2007-10-06       | 275                  |                             
|2007-10-06       | 300                  |
+----------------------------------------+

sell_master

+----------------------------------------+
|sell_date(date)  | total_amount(double) |
+----------------------------------------+
|2007-10-06       | 10                   |
+----------------------------------------+

当前结果

+---------------------------------------+
|order_date | SumOfNO | SumOfSM | Total |
+---------------------------------------+
|2007-10-06 | 1130    | 40      |1170   |
+---------------------------------------+

预期结果

+---------------------------------------+
|order_date | SumOfNO | SumOfSM | Total |
+---------------------------------------+
|2007-10-06 | 1130    | 10      |1140   |
+---------------------------------------+

推荐答案

您不应在主查询中使用 SUM(t2.total_amount).您已经在子查询中计算了总和，应该使用该总和.发生的事情是您将 t2.total_amount 乘以 new_order 中匹配的行数.

You shouldn't use SUM(t2.total_amount) in the main query. You already calculated the sum in the subquery, you should use that. What's happening is that you're multiplying t2.total_amount by the number of rows in new_order that matches.

在 SUM()中也不需要使用 IFNULL()，因为 SUM()会忽略空值(大多数聚合函数会).

There's also no need to use IFNULL() inside SUM(), since SUM() ignores null values (most aggregation functions do).

子查询应选择日期的年和月，因此您可以在这些目录中加入，而不必使用 date_format .

The subquery should select the year and month of the date, so you can join on those directory, rather than using date_format.

由于您是按月分组，因此您不应该选择 t1.order_date ，它只会从该组中随机选择一个月中的某天.您应该只以 YYYY-MM 格式显示月份.

And since you're grouping by month, you shouldn't select t1.order_date -- that will just pick a random day of the month from the group. You should just show the month in YYYY-MM format.

SELECT 
    DATE_FORMAT(t1.order_date, '%Y-%m') AS month,
    sum(t1.received_amt) as SumOfNO,
    IFNULL(t2.Amount, 0) as SumOfSM,
    sum(t1.received_amt) + IFNULL(t2.Amount, 0) AS Total
FROM `new_order` t1
LEFT JOIN
 (  select YEAR(t2.sell_date) AS year, MONTH(t2.sell_date) AS month,  sum(total_amount) as Amount
    from sell_master t2
    group by year, month
 ) t2
 ON YEAR(t1.order_date) = t2.year AND MONTH(t1.order_date) = month
GROUP BY month
ORDER BY month DESC

演示

这篇关于Mysql左连接和总和不能按月和年正确地与组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！