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

Question

我正在寻找一种通过查询连接组内字段字符串的方法。例如，我有一张 table :

<表类="s-表"><头>身份证COMPANY_ID员工<正文>11安娜21比尔32卡罗尔42戴夫

我想按 company_id 分组以获得如下内容:

<表类="s-表"><头>COMPANY_ID员工<正文>1安娜，比尔2卡罗尔，戴夫

mySQL 中有一个内置函数可以执行此操作group_concat

Answer 1

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:

请注意support for Postgres 8.4 ended in 2014 ，因此您应该出于比字符串聚合更重要的原因进行升级。

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”。这是我放在一起并在 8.3.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;