mysql left join和sum无法按月份和年份与组正常工作

9vw9lbht 于 2021-06-23 发布在 Mysql

关注(0)|答案(2)|浏览(610)

我想得到一个总金额和收到金额与集团的月和年的总和 mysql . 但我面临一个问题。问题是按月份和年份分组的第二个表列总和不正确。
当前查询

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

例子：
第一个表：新订单
第二张table：卖主
表结构：
新订单

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

出售\u 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   |
+---------------------------------------+

mysql Join multiple-columns left-join sum

来源：https://stackoverflow.com/questions/50516011/mysql-left-join-and-sum-not-working-correctly-with-group-by-month-and-year

2条答案

按热度按时间

uyhoqukh1#

你不应该使用 SUM(t2.total_amount) 在主查询中。您已经在子查询中计算了总和，应该使用它。现在发生的是你在繁殖 t2.total_amount 按中的行数 new_order 那是匹配的。
也不需要使用 IFNULL() 内部 SUM() ，自 SUM() 忽略空值（大多数聚合函数都忽略）。
子查询应该选择日期的年份和月份，这样您就可以加入这些目录，而不是使用 date_format .
因为你是按月份分组的，所以你不应该选择 t1.order_date --只需从组中随机选取一天。你应该把月份显示出来 YYYY-MM 格式。

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

演示

赞(0）回复(0）举报 2021-06-23

9o685dep2#

您要选择一个表的聚合结果（此处为总和）与另一个表的聚合结果。因此，构建两个聚合，然后加入。

select 
  year_month, 
  o.total as sumofno,
  coalesce(s.total, 0) as sumofsm,
  o.total + coalesce(s.total, 0) as total
from
(
  select format(order_date, 'yyyy-MM') as year_month, sum(received_amt) as total
  from new_order
  group by format(order_date, 'yyyy-MM')
) o
left join
(
  select format(sell_date, 'yyyy-MM') as year_month, sum(total_amount) as total
  from sell_master
  group by format(sell_date, 'yyyy-MM')
) s using (year_month)
order by month desc;

赞(0）回复(0）举报 2021-06-23

我来回答

mysql left join和sum无法按月份和年份与组正常工作

2条答案

相关问题

热门标签

最新问答