我正在寻找一种通过查询连接组内字段的字符串的方法。例如,我有一张表:
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 BY clause 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. Then array_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_agg for 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_agg except the ORDER BY clause.
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 textcat function (which lies behind the || operator):
CREATE AGGREGATE textcat_all( basetype = text, sfunc = textcat, stype = text, initcond = '' );Here is the CREATE AGGREGATE documentation.
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 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'组中的字符串字段的字符串?
发布评论