如何在 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 = ''
);
这只是将所有字符串粘合在一起,没有分隔符.为了得到一个,"插入它们之间而没有最后,您可能想要制作自己的连接函数并将其替换为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屋!