group_concat on postgresql

概述CREATE TABLE produk(seq_id serial NOT NULL,name character varying(100) NOT NULL) ;INSERT INTO produk (seq_id, name) VALUES (1, 'beer');INSERT INTO produk (seq_id, name) VALUES (2, 'in');

CREATE table produk(seq_ID serial NOT NulL,name character varying(100) NOT NulL) ;INSERT INTO produk (seq_ID,name) VALUES (1,'beer');INSERT INTO produk (seq_ID,name) VALUES (2,'in');INSERT INTO produk (seq_ID,name) VALUES (3,name) VALUES (4,'can');INSERT INTO produk (seq_ID,name) VALUES (6,'goods');INSERT INTO produk (seq_ID,name) VALUES (7,name) VALUES (8,name) VALUES (5,'goods');

MysqL:

select name,group_concat(cast(seq_ID as char)) as ID_of_duplicatesfrom produkgroup by nameorder by name;

Postgresql:

create aggregate array_accum (sfunc = array_append,basetype = anyelement,stype = anyarray,initcond = '{}');CREATE OR REPLACE FUNCTION _group_concat(text,text)RETURNS text AS $$SELECT CASEWHEN  IS NulL THEN WHEN  IS NulL THEN ELSE  operator(pg_catalog.||) ',' operator(pg_catalog.||) END$$ IMMUtable LANGUAGE sql;CREATE AGGREGATE group_concat (BASETYPE = text,SFUNC = _group_concat,STYPE = text);

first approach:

select name,array_accum(seq_ID)from produkgroup by nameselect name,array_to_string(array_accum(seq_ID),',')from produkgroup by name;

second approach (MysqL-compatible approach):

select name,group_concat(seq_ID) as ID_of_duplicatesfrom produkgroup by nameorder by name;

MysqL GROUP_CONCAT with ordering:

select name,group_concat(cast(seq_ID as char) order by seq_ID) as ID_of_duplicatesfrom produkgroup by nameorder by name;

Postgresql equivalent:

select name,group_concat(distinct seq_ID) as ID_of_duplicatesfrom produkgroup by nameorder by name;

using customized sort:

select name,group_concat(seq_ID) as ID_of_duplicatesfrom(select name,seq_IDfrom produkorder by name,seq_ID) as xgroup by name

http://s2.diffuse.it/blog/show/10-group_concat_on_postgresql

总结

以上是内存溢出为你收集整理的group_concat on postgresql全部内容，希望文章能够帮你解决group_concat on postgresql所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。