如何在 PostgreSQL 'group by' 查询中连接字符串字段的字符串? [英] How to concatenate strings of a string field in a PostgreSQL 'group by' query?

问题描述

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

I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table:

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

我想按 company_id 分组以获得类似的内容:

and I wanted to group by company_id to get something like:

COMPANY_ID   EMPLOYEE
1            Anna, Bill
2            Carol, Dave

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

There is a built-in function in mySQL to do this group_concat

推荐答案

PostgreSQL 9.0 或更高版本:

现代 Postgres(自 2010 年起)具有 string_agg(expression, delimiter) 函数将完全满足提问者的要求:

PostgreSQL 9.0 or later:

Modern Postgres (since 2010) has the string_agg(expression, delimiter) function which will do exactly what the asker was looking for:

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

Postgres 9 还添加了指定 ORDER BY 子句的功能 在任何聚合表达式中;否则，您必须对所有结果进行排序或处理未定义的顺序.所以你现在可以写:

Postgres 9 also added the ability to specify an ORDER BY clause in any aggregate expression; otherwise you have to order all your results or deal with an undefined order. So you can now write:

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

PostgreSQL 8.4.x:

PostgreSQL 8.4(2009 年)引入了 聚合函数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 及更早版本:

最初提出这个问题时，没有内置聚合函数来连接字符串.最简单的自定义实现(由 Vajda Gabo 在此邮件列表帖子中建议 等)是使用内置的 textcat 函数(位于 || 操作符后面):

PostgreSQL 8.3.x and older:

When this question was originally posed, there was no built-in aggregate function to concatenate strings. The simplest custom implementation (suggested by Vajda Gabo in this mailing list post, among many others) is to use the built-in textcat function (which lies behind the || operator):

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

这里是 CREATE AGGREGATE 文档.

这只是将所有字符串粘合在一起，没有分隔符.为了得到一个，"插入它们之间而没有最后，您可能想要制作自己的连接函数并将其替换为textcat"以上.这是我放在一起并在 8.3.12 上测试的:

This simply glues all the strings together, with no separator. In order to get a ", " inserted in between them without having it at the end, you might want to make your own concatenation function and substitute it for the "textcat" above. Here is one I put together and tested on 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;

这个版本即使行中的值为空或空也会输出一个逗号，所以你会得到这样的输出:

This version will output a comma even if the value in the row is null or empty, so you get output like this:

a, b, c, , e, , g

如果您希望删除多余的逗号来输出:

If you would prefer to remove extra commas to output this:

a, b, c, e, g

然后向函数添加一个ELSIF检查，如下所示:

Then add an ELSIF check to the function like this:

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;

这篇关于如何在 PostgreSQL 'group by' 查询中连接字符串字段的字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！