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

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}U内容与道具关联。这两个p_内容具有指向相应内容的相同库id。
因此，p.value的和是应该的两倍，因为有两个p\u内容指向相同的内容
我怎样才能不使p值加倍呢？
编辑：
我知道如何使用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')

最佳答案

这里有一个解决方案。首先将集合缩减为派生表中的不同行，然后将GROUPBY应用于该结果：

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不是函数；它是一个查询修饰符。

关于mysql - 当我将SUM和COUNT与多个INNER JOINS一起使用时，MySQL如何不包括重复的行？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/7351462/