如何在 PostgreSQL 'group by' 查询中连接字符串字段的字符串

发布于09月04日

我正在寻找一种方法，通过查询将组中字段的字符串连接起来.例如，我有一张桌子:

ID   COMPANY_ID   EMPLOYEE
1    1            Anna
2    1            Bill
3    2            Carol
4    2            Dave

我想按公司id分组，得到如下信息:

COMPANY_ID   EMPLOYEE
1            Anna, Bill
2            Carol, Dave

mySQL中有一个内置函数来实现这一点

推荐答案

PostgreSQL 9.0或更高版本:

现代Postgres(自2010年起)有string_agg(expression, delimiter)个功能，可以完全满足提问者的要求:

SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;

Postgres 9还增加了指定ORDER BY第in any aggregate expression条的能力；否则，您必须对所有结果进行排序，或者处理未定义的顺序.所以你现在可以写:

SELECT company_id, string_agg(employee, ', ' ORDER BY employee)
FROM mytable
GROUP BY company_id;

PostgreSQL 8.4.x:

PostgreSQL 8.4(2009年)引入了the aggregate function array_agg(expression)，用于收集数组中的值.然后可以使用array_to_string()来获得所需的结果:

SELECT company_id, array_to_string(array_agg(employee), ', ')
FROM mytable
GROUP BY company_id;

PostgreSQL 8.3.x及以上:

最初提出这个问题时，没有内置的聚合函数来连接字符串.最简单的自定义实现(suggested by Vajda Gabo in this mailing list post等)是使用内置textcat函数(位于||运算符后面):

CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

Here is the CREATE AGGREGATE documentation.

这只是把所有的线粘在一起，没有分隔符.为了在它们之间插入一个"，"而不在末尾，您可能需要创建自己的连接函数，并用它代替上面的"textcat".1.8和12放在一起测试:

CREATE FUNCTION commacat(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;

即使行中的值为null或空，此版本也会输出逗号，因此您会得到如下输出:

a, b, c, , e, , g

如果您希望删除额外的逗号以输出:

a, b, c, e, g

然后向函数中添加一个ELSIF判断，如下所示:

CREATE FUNCTION commacat_ignore_nulls(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSIF instr IS NULL OR instr = '' THEN
      RETURN acc;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;