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

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

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

我想通过company_id进行分组，以获得如下内容:

COMPANY_ID   EMPLOYEE
1            Anna, Bill
2            Carol, Dave

在mySQL中有一个内置函数来做这个group_concat

392465 次浏览
小开

这个答案不是我的功劳，因为我是经过一番搜索才找到的:

我不知道的是PostgreSQL允许你用创建聚合定义你自己的聚合函数

PostgreSQL列表上的这篇文章显示了创建一个函数来做所需的事情是多么简单:

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


SELECT company_id, textcat_all(employee || ', ')
FROM mytable
GROUP BY company_id;
小开
最佳答案

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子句在任何聚合表达式中;否则，您必须对所有结果进行排序或处理未定义的顺序。现在你可以这样写:

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函数(位于||操作符后面):

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

这里是CREATE AGGREGATE文档

这只是将所有字符串粘在一起，没有分隔符。为了得到一个"， "插入它们之间，而不是在结尾，你可能想要创建自己的连接函数，并将其替换为“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;
小开

如前所述，创建自己的聚合函数是正确的做法。下面是我的串联聚合函数(你可以找到法语详细资料):

CREATE OR REPLACE FUNCTION concat2(text, text) RETURNS text AS '
SELECT CASE WHEN $1 IS NULL OR $1 = \'\' THEN $2
WHEN $2 IS NULL OR $2 = \'\' THEN $1
ELSE $1 || \' / \' || $2
END;
'
LANGUAGE SQL;


CREATE AGGREGATE concatenate (
sfunc = concat2,
basetype = text,
stype = text,
initcond = ''

）;

然后把它用作:

SELECT company_id, concatenate(employee) AS employees FROM ...
小开

如果你要升级到8.4，这个最新的公告列表片段可能会感兴趣:

直到8.4推出 超级高效的本地一个，你可以添加 类中的array_accum()函数 用于滚动的PostgreSQL文档 将任何列放入数组，这可以 然后被应用程序代码使用，或者 结合array_to_string()来 格式为列表:

http://www.postgresql.org/docs/current/static/xaggr.html

我想链接到8.4的开发文档，但他们似乎还没有列出这个功能。

小开

我发现这个PostgreSQL文档很有用:http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html。

在我的例子中，如果字段不为空，我使用纯SQL将字段用括号连接起来。

select itemid,
CASE
itemdescription WHEN '' THEN itemname
ELSE itemname || ' (' || itemdescription || ')'
END
from items;
小开

使用Postgres文档继续Kev的回答:

首先，创建一个元素数组，然后使用内置的array_to_string函数。

CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);


select array_to_string(array_accum(name),'|') from table group by id;
小开

下面再次介绍字符串连接的自定义聚合函数的使用:您需要记住，select语句将以任意顺序排列行，因此您需要在从语句中执行带有命令子句的子选择，然后执行带有集团子句的外部选择来聚合字符串，从而:

SELECT custom_aggregate(MY.special_strings)
FROM (SELECT special_strings, grouping_column
FROM a_table
ORDER BY ordering_column) MY
GROUP BY MY.grouping_column
小开

如何使用Postgres内置数组函数?至少在8.4中，这是开箱即用的:

SELECT company_id, array_to_string(array_agg(employee), ',')
FROM mytable
GROUP BY company_id;
小开
从PostgreSQL 9.0开始，你可以使用名为string_agg的聚合函数。你的新SQL应该看起来像这样:
SELECT company_id, string_agg(employee， '， ')
从mytable
GROUP BY company_id;

.
小开

根据PostgreSQL 9.0及以上版本，你可以使用名为string_agg的聚合函数。你的新SQL应该看起来像这样:

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

你也可以使用format函数。它本身也可以隐式地处理text、int等类型转换。

create or replace function concat_return_row_count(tbl_name text, column_name text, value int)
returns integer as $row_count$
declare
total integer;
begin
EXECUTE format('select count(*) from %s WHERE %s = %s', tbl_name, column_name, value) INTO total;
return total;
end;
$row_count$ language plpgsql;




postgres=# select concat_return_row_count('tbl_name','column_name',2); --2 is the value
小开

我使用Jetbrains Rider，从上面的例子中复制结果并重新执行是一件麻烦的事情，因为它似乎将所有内容都包装在JSON中。这将它们连接到一个更容易运行的语句中

select string_agg('drop table if exists "' || tablename || '" cascade', ';')
from pg_tables where schemaname != $$pg_catalog$$ and tableName like $$rm_%$$
小开

如果您使用的是Amazon Redshift，不支持string_agg，请尝试使用listagg。

SELECT company_id, listagg(EMPLOYEE, ', ') as employees
FROM EMPLOYEE_table
GROUP BY company_id;
小开

对PostgreSQL和谷歌BigQuery SQL使用STRING_AGG函数:

SELECT company_id, STRING_AGG(employee, ', ')
FROM employees
GROUP BY company_id;

提交答案