当我将 SUM 和 COUNT 与多个 INNER JOINS 一起使用时，MySQL 如何不包含重复行？

Question

SELECT SUM(case when p.status = 2 then p.value end) as 'val_accepted'
      FROM
        props AS p
            INNER JOIN (p_contents AS pc
              INNER JOIN contents AS c ON c.id = pc.library_id)
            ON p.id = pc.prop_id
      WHERE p.account_id = 3
      GROUP BY (pc.library_id)

所以，发生了什么:有两个 p_contents 与 Prop 相关联。这两个 p_contents 具有相同的 library_id 指向相应的内容。
所以，p.value 的 SUM 是它应该的两倍，因为有两个 p_contents 指向相同的内容

如何不将 p.value 的 SUM 加倍？

编辑:
我想出了如何使用 DISTINCT，但我仍然需要访问内部列...

SELECT    c.name as 'library_name',
   SUM(case when p.status = 2 then p.value end) as 'val_accepted',

FROM
  props AS p
  INNER JOIN 
  (
    SELECT DISTINCT(pc.library_id), prop_id
    FROM prop_contents AS pc
    INNER JOIN 
    (
      SELECT name, visibility, id, updated_at 
      FROM contents AS c
    ) as c
      ON c.id = pc.library_id
  )as pc
  ON p.id = pc.prop_id
 WHERE p.account_id = 3
 GROUP BY (pc.library_id)

现在我得到了错误:

Unknown column 'c.name' in 'field list')

Answer 1

这是一种解决方案。首先将集合减少到派生表中的不同行，然后将 GROUP BY 应用于该结果:

SELECT SUM(case when d.status = 2 then d.value end) as 'val_accepted'
FROM (
  SELECT DISTINCT p.id, p.status, p.value, pc.library_id
  FROM props p
  INNER JOIN p_contents AS pc ON p.id = pc.prop_id
  INNER JOIN contents AS c ON c.id = pc.library_id
  WHERE p.account_id = 3) AS d
GROUP BY d.library_id

---

您在示例中使用了 DISTINCT(pc.library_id)，就好像 DISTINCT 仅适用于括号内的列一样。这是一个普遍的误解。 DISTINCT 适用于选择列表的所有列。 DISTINCT 不是函数；它是一个查询修饰符。