PostgreSQL数组含重复key时SUM函数未重复累加的解决需求

你的问题出在ANY操作符的特性上：当用d.id = ANY(w.data_ids)匹配时，PostgreSQL会从data表中找出所有匹配数组中ID的行，但每个唯一ID只会返回一次，不管它在数组里出现多少次，所以SUM自然只计算了去重后的总和。

要实现重复ID的累加，你需要先把数组拆分成单独的行，让每个重复的ID都成为独立的记录，再关联data表求和。用unnest()函数就能做到这一点：

SELECT
    w.id,
    wc.name,
    wc.description,
    w.data_ids,                                     
    (SELECT SUM(d.length) 
     FROM unnest(w.data_ids) AS unnest_id
     JOIN data d ON d.id = unnest_id) AS length
FROM words w
LEFT JOIN "words_content" wc ON wc.word_id = w.id
WHERE 
w.id = 15
AND
wc.language_id = 2

原理说明： #

1. unnest(w.data_ids)把数组[1,2,3,1,2,3]拆成6行记录，每行对应一个数组元素：

   unnest_id
   ----------
    1
    2
    3
    1
    2
    3
   

2. 用这些行和data表关联，每个unnest_id都会匹配到对应的d.length，得到6个值：1、1、4、1、1、4
3. 最后SUM这些值，结果就是12，符合你的预期。

如果数组中可能存在NULL元素，你可以在子查询里加个过滤条件避免影响结果：

(SELECT SUM(d.length) 
 FROM unnest(w.data_ids) AS unnest_id
 JOIN data d ON d.id = unnest_id
 WHERE unnest_id IS NOT NULL) AS length

内容的提问来源于stack exchange，提问作者Aldinho