如何删除重复，这是由 array_agg postgres 函数生成的

有人知道如何重写以下 SQL 查询来生成只包含一个名称匹配项的结果吗？(按使用者分组的结果)。

那个问题

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

可执行查询在 译自: 美国《每日邮报》网站(http://sqlfiddle.com/# ! 15/b911f/5)上可用。单击 RunSQL 按钮，将得到两次包含 Frantisek Smith的结果

67210 次浏览

小开

最佳答案

You can use the distinct keyword inside 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

SQLFiddle with this example

小开

No need to go all round the houses with ARRAY_TO_STRING(ARRAY_AGG( when a simple STRING_AGG will do as follows (code available on the fiddle here):

--
-- Simplified
--


SELECT
DISTINCT
STRING_AGG
(
DISTINCT CONCAT(w.firstname, ' ', w.lastname), ', '
) AS "The workers"
FROM log_item li
INNER JOIN log l ON li.log_id = l.id
INNER JOIN worker w ON l.worker_id = w.id
WHERE li.company_id = 1;

Result:

                The workers
Frantisek Smith, Peter Duff