SQL 如何删除使用 array_agg postgres 函数生成的重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26363742/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to remove duplicates, which are generated with array_agg postgres function
提问by Peter Jurkovic
Does anyone an idea how to rewrite following SQL query to generate results, that would contains only one occurrence of name? (results grouped by user).
有没有人知道如何重写以下 SQL 查询以生成结果，该查询仅包含一次名称？（结果按用户分组）。
The query
查询
SELECT array_to_string(array_agg(CONCAT(u.firstname, ' ', u.lastname)), ', ')
FROM log_has_item logitem
INNER JOIN log log ON log.id = logitem.log_id
INNER JOIN worker u ON log.worker_id = u.id
WHERE logitem.company_id = 1
Executable query is avaiable on sqlfiddle.com. Click on Run SQL button and you will result, which contains Frantisek Smithtwice
可执行查询在sqlfiddle.com上可用。单击 Run SQL 按钮，您将得到结果，其中包含Frantisek Smith两次
回答by Mureinik
You can use the distinctkeyword inside array_agg:
您可以在distinct里面使用关键字array_agg：
SELECT ARRAY_TO_STRING(ARRAY_AGG(DISTINCT CONCAT(u.firstname, ' ', u.lastname)), ', ')
FROM log_has_item logitem
INNER JOIN log log ON log.id = logitem.log_id
INNER JOIN worker u ON log.worker_id = u.id
WHERE logitem.company_id = 1
