group_concat on postgresql
发布时间:2020-12-13 17:46:11 所属栏目:百科 来源:网络整理
导读: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 (
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_duplicates from produk group by name order by name;
create aggregate array_accum ( sfunc = array_append,basetype = anyelement,stype = anyarray,initcond = '{}' ); CREATE OR REPLACE FUNCTION _group_concat(text,text) RETURNS text AS $$ SELECT CASE WHEN $2 IS NULL THEN $1 WHEN $1 IS NULL THEN $2 ELSE $1 operator(pg_catalog.||) ',' operator(pg_catalog.||) $2 END $$ IMMUTABLE LANGUAGE SQL; CREATE AGGREGATE group_concat ( BASETYPE = text,SFUNC = _group_concat,STYPE = text );
select name,array_accum(seq_id) from produk group by name select name,array_to_string(array_accum(seq_id),',') from produk group by name;
select name,group_concat(seq_id) as id_of_duplicates from produk group by name order by name;
select name,group_concat(cast(seq_id as char) order by seq_id) as id_of_duplicates from produk group by name order by name;
select name,group_concat(distinct seq_id) as id_of_duplicates from produk group by name order by name;
select name,group_concat(seq_id) as id_of_duplicates from ( select name,seq_id from produk order by name,seq_id ) as x group by name
(编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |