如何通过查询来连接PostgreSQL'组中的字符串字段的字符串？ [英] How to concatenate strings of a string field in a PostgreSQL 'group by' query?
问题描述
我正在寻找一种通过查询连接组内字段的字符串的方法。例如，我有一张表：
ID COMPANY_ID员工
1 1安娜
2 1账单
3 2 Carol
4 2 Dave
我想按group_id得到像这样的东西：
COMPANY_ID雇员
1 Anna，Bill
2 Carol，Dave
在mySQL中有一个内置函数来执行此操作 group_concat
解决方案
PostgreSQL 9.0或更高版本：
最新版本的Postgres（自2010年年底起）拥有 一个>功能，它将完成问题的要求，例如，让你指定分隔符字符串：
SELECT company_id，string_agg（employee，'，'）
FROM mytable
GROUP BY company_id;
Postgres 9.0还增加了指定 ORDER BY 子句任何聚合表达式;否则，订单未定义。所以你现在可以这样写：
SELECT company_id，string_agg（employee，'，'ORDER BY employee）
FROM mytable
GROUP BY company_id;
确实：
选择string_agg（actor_name，'，'ORDER BY first_appearance）
PostgreSQL 8.4或后来：
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;对于9.0以前的版本，
string_agg ：
如果有人遇到这种情况，需要为9.0之前的数据库寻找兼容填充，可以在 string_agg 除了 ORDER BY 子句。
所以在下面的定义中，它应该和9中的一样.x Postgres DB：
SELECT string_agg（name，';'）AS semi_colon_separated_names FROM things;
但这会是一个语法错误：
SELECT string_agg（name，';'ORDER BY name）AS semi_colon_separated_names FROM things;
- >错误：在ORDER处或附近出现语法错误
测试PostgreSQL 8.3。
CREATE FUNCTION string_agg_transfn（text，text，text）
RETURNS文本AS
$$
BEGIN
IF $ 1 IS NULL THEN
RETURN $ 2;
ELSE
RETURN $ 1 || $ 3 || $ 2;
END IF;
END;
$$
语言plpgsql IMMUTABLE
COST 1;
CREATE AGGREGATE string_agg（text，text）（
SFUNC = string_agg_transfn，
STYPE = text
）;
自定义变体（所有Postgres版本）
在9.0之前，没有内置聚合函数来连接字符串。最简单的自定义实现（Vajda Gabo在此邮件列表中发布的 等等）是使用内置的 textcat 函数（它位于 || 运算符后面） ：
$ b $ pre $ CREATE AGGREGATE textcat_all（
basetype = text，
sfunc = textcat，
stype = text，
initcond =''
）;
文档。 >这简单地将所有的字符串粘在一起，没有分隔符。为了在它们之间插入一个，而不是最后，你可能想要创建自己的连接函数并将其替换为上面的textcat。以下是我在8.3.12上进行测试的结果：
CREATE FUNCTION commacat（acc text，instr text）返回文本AS $$
BEGIN
IF acc IS NULL OR acc =''THEN
RETURN instr;
ELSE
RETURN acc || '，'|| INSTR;
END IF;
END;
$$语言plpgsql;
即使行中的值为null或空，该版本也会输出逗号，输出如下：
a，b，c，，e，，g
如果您希望删除多余的逗号以输出此内容：
a，b，c，e，g
然后添加一个
ELSIF检查如下函数：
CREATE FUNCTION commacat_ignore_nulls（acc text，instr text）返回文本AS $$
BEGIN
IF acc IS NULL OR acc =''THEN
RETURN instr;
ELSIF instr IS NULL或instr =''THEN
RETURN acc;
ELSE
RETURN acc || '，'|| INSTR;
END IF;
END;
$$语言plpgsql;
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 Daveand I wanted to group by company_id to get something like:
COMPANY_ID EMPLOYEE 1 Anna, Bill 2 Carol, DaveThere is a built-in function in mySQL to do this group_concat
解决方案PostgreSQL 9.0 or later:
Recent versions of Postgres (since late 2010) have the
string_agg(expression, delimiter)function which will do exactly what the question asked for, even letting you specify the delimiter string:SELECT company_id, string_agg(employee, ', ') FROM mytable GROUP BY company_id;Postgres 9.0 also added the ability to specify an
ORDER BYclause in any aggregate expression; otherwise, the order is undefined. So you can now write:SELECT company_id, string_agg(employee, ', ' ORDER BY employee) FROM mytable GROUP BY company_id;Or indeed:
SELECT string_agg(actor_name, ', ' ORDER BY first_appearance)
PostgreSQL 8.4 or later:
PostgreSQL 8.4 (in 2009) introduced the aggregate function
array_agg(expression)which concatenates the values into an array. Thenarray_to_string()can be used to give the desired result:SELECT company_id, array_to_string(array_agg(employee), ', ') FROM mytable GROUP BY company_id;
string_aggfor pre-9.0 versions:In case anyone comes across this looking for a compatibilty shim for pre-9.0 databases, it is possible to implement everything in
string_aggexcept theORDER BYclause.So with the below definition this should work the same as in a 9.x Postgres DB:
SELECT string_agg(name, '; ') AS semi_colon_separated_names FROM things;But this will be a syntax error:
SELECT string_agg(name, '; ' ORDER BY name) AS semi_colon_separated_names FROM things; --> ERROR: syntax error at or near "ORDER"Tested on PostgreSQL 8.3.
CREATE FUNCTION string_agg_transfn(text, text, text) RETURNS text AS $$ BEGIN IF $1 IS NULL THEN RETURN $2; ELSE RETURN $1 || $3 || $2; END IF; END; $$ LANGUAGE plpgsql IMMUTABLE COST 1; CREATE AGGREGATE string_agg(text, text) ( SFUNC=string_agg_transfn, STYPE=text );
Custom variations (all Postgres versions)
Prior to 9.0, 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
textcatfunction (which lies behind the||operator):CREATE AGGREGATE textcat_all( basetype = text, sfunc = textcat, stype = text, initcond = '' );Here is the
CREATE AGGREGATEdocumentation.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, , gIf you would prefer to remove extra commas to output this:
a, b, c, e, gThen add an
ELSIFcheck 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'组中的字符串字段的字符串？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
