我正在寻找一种通过查询连接组内字段的字符串的方法。例如，我有一张表：
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'组中的字符串字段的字符串？
发布评论