如何使用 Distinct/group by 和 JOINS 来过滤行中的重复项

Question

为简单起见，我将给出一个我想要实现的目标的快速示例：

表 1 - Members

  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 - Selection_Details

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

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

结果应该显示：

结果表，

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

但约翰的值为 $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 购买了它）。

任何帮助表示赞赏。

编辑

还有另一个表我忘了提及，它是代理表（将计划出售给会员的代理）。

最终的 group by 语句对按代理 ID 销售的所有商品进行分组（这会将最终结果转换为一行）。

Answer 1

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

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

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

评论后续：

刚刚看到您关于“更改忽略...”的评论。这工作得很好，但表中仍然会留下错误的重复项。我建议执行唯一键，然后手动清理表。我在评论中输入的查询应该为您找到所有重复项，然后您可以手动删除它们。一旦表干净，就不需要查询的重复处理版本。

Answer 2

使用 UNIQUE 键可防止意外重复输入。这将从源头上消除问题，而不是等到问题开始出现症状时才解决。它还使以后的查询变得更容易，因为您可以依靠拥有一致的数据库。

Answer 3

怎么样：

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

顺便说一句，是否有一个原因导致您在 member_selections 上没有主键来防止这些重复发生？

Answer 4

您可以在内部查询中添加一个 group by 子句，该子句按所有三列进行分组，基本上只返回唯一的行。 （我还将“溢价”更改为“成本”以匹配您的示例表，并删除了代理部分）

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

        GROUP BY
            m.ID,
            m.NAME,
            g.Cost
) sq 
group by 
    sq.ID,
    sq.NAME