当计数与string_agg一起使用时，在postgres中没有正确计数吗？

Question

SELECT concepts.*, 
string_agg(DISTINCT concept_skill, ', ') AS skillsSortedById, 
COUNT (likes.concept_id) AS counted
FROM concepts
INNER JOIN concept_skills ON 
(concepts.concept_id=concept_skills.concept_id)
INNER JOIN likes ON (concepts.concept_id=likes.concept_id)
GROUP BY concept_skills.concept_id, concepts.concept_id
ORDER BY counted desc;

不知怎么的，当使用计数与string_agg计数相乘时，每一个都是4的乘数，给出了错误的结果，但是如果我使用不带string_agg的计数，它会给出确切的结果，有人能帮上忙吗？

Answer 1

查询的问题是，您在加入后正在进行计数。

SELECT q.*,  COUNT(l.concept_id) AS counted
FROM (
  SELECT concepts.*, 
  string_agg(DISTINCT concept_skill, ', ') AS skillsSortedById 
  FROM concepts
  INNER JOIN concept_skills ON 
  (concepts.concept_id=concept_skills.concept_id)
  GROUP BY concept_skills.concept_id, concepts.concept_id
) q, likes l
WHERE q.concept_id = l.concept_id
GROUP BY q.concept_id, q.skillsSortedById
ORDER BY counted desc;

见我编辑的SQL Fiddle