postgresql 查询集合结果用逗号分隔返回字符串处理的操作_PostgreSQL

postgresql 查询集合结果用逗号分隔返回字符串处理的操作

2021-04-12 17:04dbsjack PostgreSQL

这篇文章主要介绍了postgresql 查询集合结果用逗号分隔返回字符串处理的操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧

关键字：

1	`string_agg(''` `,` `'')`

例如：

1	`select` `string_agg(name\|\|''` `,` `',')` `from` `sys_user`

postgresql 查询集合结果用逗号分隔返回字符串处理的操作

补充：PostgreSQL 字段用逗号 “，”隔开判断是否含有某个值

Array Functions and Operators

https://www.postgresql.org/docs/9.2/functions-array.html

									-- ----------------------------

									-- Table structure for T_STUDENT

									-- ----------------------------

									DROP TABLE IF EXISTS "public"."T_STUDENT";

									CREATE TABLE "public"."T_STUDENT" (

									"id" int4,

									"name" varchar(255) COLLATE "default",

									"course" varchar(255) COLLATE "default"

									)

									WITH (OIDS=FALSE)

									;

									-- ----------------------------

									-- Records of T_STUDENT

									-- ----------------------------

									INSERT INTO "public"."T_STUDENT" VALUES ('1', '李四', '12,45,1,66,7,89');

									INSERT INTO "public"."T_STUDENT" VALUES ('2', '刘一', '1,5,8,9');

									INSERT INTO "public"."T_STUDENT" VALUES ('3', '王五', '0,4,2');

									INSERT INTO "public"."T_STUDENT" VALUES ('4', '张三', '1,2,5,7');

									-- ----------------------------

									-- Alter Sequences Owned By 

									-- ----------------------------

									-- ----------------------------

									-- Table structure for T_STUDENT

									-- ----------------------------

									DROP TABLE IF EXISTS "public"."T_STUDENT";

									CREATE TABLE "public"."T_STUDENT" (

									"id" int4,

									"name" varchar(255) COLLATE "default",

									"course" varchar(255) COLLATE "default"

									)

									WITH (OIDS=FALSE)

									;

									-- ----------------------------

									-- Records of T_STUDENT

									-- ----------------------------

									INSERT INTO "public"."T_STUDENT" VALUES ('1', '李四', '12,45,1,66,7,89');

									INSERT INTO "public"."T_STUDENT" VALUES ('2', '刘一', '1,5,8,9');

									INSERT INTO "public"."T_STUDENT" VALUES ('3', '王五', '0,4,2');

									INSERT INTO "public"."T_STUDENT" VALUES ('4', '张三', '1,2,5,7');

									-- ----------------------------

									-- Alter Sequences Owned By 

									-- ----------------------------

									id name course

									4   张三  1,2,5,7

									1   李四  12,45,1,5,66,7,89

									2   刘一  1,5,8,9

									3   王五  0,4,2

									SELECT * FROM "public"."T_STUDENT" WHERE string_to_array(course, ',') @> ARRAY['2','7']

									结果：

									id name course

									4   张三  1,2,5,7

									SELECT * FROM "public"."T_STUDENT" WHERE string_to_array(course, ',') <@ array['5','12','45','1','0','4','2']

									结果：

									id name course

									3   王五  0,4,2

									SELECT * FROM "public"."T_STUDENT" WHERE string_to_array(course, ',') && ARRAY['5','8','225','111']

									结果：

									id name course

									4   张三  1,2,5,7

									2   刘一  1,5,8,9