mysql - 使用 SUM 进行 3 个表的 SQL 查询不起作用

Question

我有 3 张 table

tb预测

创建者[varchar] - 日期[日期] - 价格[double] - 状态[int]

TB扇区

扇区[varchar] - 用户[varchar]

待定目标

用户[varchar] - 目标[double] - 月[int] - 年[int]

我需要一个查询来汇总所选月份和年份的值并按部门分组，如下所示:

sector      sum(price)    sum(goal)<br/>
production  5000.00       30000.00<br/>
sales       42000.00      150000<br/> <br/>

我的实际查询没有对目标的正确值进行求和...比真实的值还要多..

SELECT s.sector, sum(m.goal), sum(f.price) AS price
FROM tbsector AS s
JOIN tbgoals AS m ON m.user = s.user
JOIN tbforecasts AS f ON f.creator = s.user
WHERE m.month = 6 AND m.year = 2016
AND month(f.date) = 6 AND year(f.date) = 2016
GROUP BY s.sector;

Answer 1

它们需要单独求和，请记住，如果没有分组和求和，类似的查询将根据相同的条件将每个 tbgoals 记录与每个 tbforecasts 记录进行匹配用户|创建者；因此，如果某个“部门”有 2 个目标和 3 个预测，您将获得(并求和)该部门的 6 个结果。

有很多方法可以解决这个问题，最常见的是:将其中一个总和放入子查询中，然后将其连接到涉及的其他表以获得另一个总和；或者将两个总和放入单独的子查询中，然后将它们连接到公共(public)表中。

从您目前提供的信息来看，尚不清楚哪一个(如果有的话)最方便；因为数据的关联方式(而不仅仅是其结构)很重要。

我提到的第二种方法看起来像这样:

SELECT s.sector, totalGoals, totalPrices 
FROM tbsector AS s 
LEFT JOIN (SELECT user, sum(goal) AS totalGoals 
      FROM tbgoals 
      WHERE `month` = 6 AND `year` = 2016
      GROUP BY user
) AS m ON m.user = s.user 
LEFT JOIN (SELECT creator, sum(price) AS totalPrices 
      FROM tbforecasts 
      WHERE month(f.`date`) = 6 AND year(f.`date`) = 2016
) AS f ON f.creator = s.user      
GROUP BY s.sector;

我猜测这是原始查询的 WHERE 条件中更合适的解决方案(请注意，我将它们移至相关子查询中)。

旁注:如果在设计过程中还不算太晚，我建议将表的某些字段重命名为与 MySQL 关键字/保留字不同；例如:日期、月份、年份，从技术上讲甚至是用户(尽管这通常不会导致太多问题)。