mysql - GROUP BY 上的三个表联接不会产生正确的结果

Question

我有 3 张 table ，即。操作、来源和访问，以及下面给出的一些示例数据:

<强>1。行动:

<强>2。来源:

<强>3。访问次数:

现在，我们想知道通过每种来源类型的访问量，此外还想分别检查通过联系和销售进行的访问量。仍在使用的查询是:

SELECT COUNT(v.visits_id) AS visits, s.source_type AS source, 
SUM(a.action_contact) AS contact, SUM(a.action_sale) AS sale FROM visits AS v JOIN sources AS s ON v.source_id = s.source_id JOIN actions AS a ON s.source_id = a.source_id 
GROUP BY source ORDER BY visits DESC

输出:

因此，如果您查看输出，您会发现访问次数恰好等于每行的联系次数和销售次数之和。所以看起来还可以。但是，如果将所有行的所有访问次数相加，则它不等于 31。数字为 47，这是不可能的，因为我们的访问表中只有 31 次访问。因此，输出给出的访问次数不得大于访问表的访问次数。这是我在查询中一直遇到的问题。如何得出必须符合原表的正确结果？

更新的表格操作:

Answer 1

这是因为您正在加入多对多操作，以通过来源进行访问。理想情况下，您将拥有与每个操作相关联的访问，但您也可以使用以下内容:

SELECT s.source_type AS source
, count(v.visits_id) as visits
, ifnull((SELECT SUM(a1.action_contact) FROM actions a1 WHERE s.source_id = a1.source_id and a1.action_contact=1 
and a1.action_date between "2016-07-01 00:00:00" and "2016-07-31 23:59:59" -- filters date
),0) as contacts
, ifnull((SELECT SUM(a2.action_sale) FROM actions a2 WHERE s.source_id = a2.source_id and a2.action_sale=1 and a2.action_date between "2016-07-01 00:00:00" and "2016-07-31 23:59:59"),0) as sales
, ifnull((SELECT SUM(a3.order_value) FROM actions a3 WHERE s.source_id = a3.source_id and a3.action_date between "2016-07-01 00:00:00" and "2016-07-31 23:59:59"),0) as sum_of_order_values
FROM visits v 
JOIN sources s ON v.source_id = s.source_id 
WHERE v.visitor_date between "2016-07-01 00:00:00" and "2016-07-31 23:59:59"
GROUP BY source 
ORDER BY visits DESC

这将为每个来源返回一条记录，访问量按正常情况计数，但联系人和销售额将作为子查询求和，因此它们不会乘以同一来源的访问次数。如果您不熟悉多对多联接及其对记录数量(以及计数和求和函数)的影响，您可以在此处查看一些示例:http://www.phpknowhow.com/mysql/many-to-many-relationships/

编辑:我刚刚添加了 sum_of_order_values 作为第三个子查询，如果您的表很大，这将确实减慢您的查询速度。从您的评论的声音来看，您打算将访问id添加到操作表中，一旦完成，您将能够将每个操作作为不同的表保留连接，这将极大地提高您的查询性能，前提是您已正确对列建立索引

再次编辑:我刚刚将日期添加到 WHERE 子句中，请注意时区等。您可以“设置”session variables因此您可以一次性更新它们，而不是单独更新。即添加

set @start_date = "2016-07-01 00:00:00";
set @end_date = "2016-07-31 23:59:59";

在查询开始处，然后在 WHERE 子句中的 @start_date 和 @end_date 之间使用 和 a3.action_date