mysql - 如何使用 JOINS 使用 Distinct/group by 过滤行内的重复项

Question

为简单起见，我将举一个简单的例子来说明我正在努力实现的目标:

表 1 - 成员

  ID    |   Name
--------------------
  1     |   John    
  2     |   Mike    
  3     |   Sam  

表 1 - Member_Selections

  ID    |   planID
--------------------
  1     |   1    
  1     |   2    
  1     |   1    
  2     |   2    
  2     |   3    
  3     |   2    
  3     |   1    

表 3 - 选择_详细信息

planID  |   Cost
--------------------
  1     |   5    
  2     |   10    
  3     |   12  

当我运行查询时，我想返回按成员分组的所有成员选择的总和。然而，我面临的问题(例如表 2 数据)是某些成员可能在系统中错误地拥有重复信息。虽然我们尽最大努力预先过滤这些数据，但有时它会从裂缝中溜走，所以当我对系统进行必要的调用以提取信息时，我也想过滤这些数据。

结果应该显示:

结果表

ID  |    Name    | Total_Cost
-----------------------------
1   |    John    |   15
2   |    Mike    |   22
3   |    Sam     |   15

而是将 John 设为 20 美元，因为他错误地插入了计划 ID #1 两次。

目前我的查询是:

SELECT
    sq.ID, sq.name, SUM(sq.premium) AS total_cost
FROM
(
    SELECT
    m.id, m.name, g.premium
    FROM members m
    INNER JOIN member_selections s USING(ID)
    INNER JOIN selection_details g USING(planid)
) sq group by sq.agent

添加 DISTINCT s.planID 会错误地过滤结果，因为它只会显示已售出的单个 PlanID 1(即使成员 1 和 3 购买了它)。

感谢任何帮助。

编辑

还有一个表忘记说了，就是agent表(向成员(member)卖plan的agent)。

最后的 group by 语句将所有销售的商品按代理 ID 分组(这会将最终结果变成一行)。

Answer 1

也许最简单的解决方案是在 member_selections 表上放置一个唯一的组合键:

 alter table member_selections add unique key ms_key (ID, planID);

这将阻止在 ID/planID 的唯一组合已存在于表中其他位置的情况下添加任何记录。那将只允许一个 (1,1)

评论跟进:

刚刚看到您对“改变忽略...”的评论。这很好，但你仍然会在表中留下错误的重复项。我建议做唯一键，然后手动清理表。我在评论中提出的查询应该会为您找到所有重复项，然后您可以手动清除这些重复项。一旦表干净，就不需要查询的重复处理版本。