PGSQL将数组中的ID列表，转换为 中文标签名；

烧脑的SQL

label表用来保存标签 的值 。（id,name,parentid ，code基本上没有用到；）

client表中有一个label整形数组字段，存储的是标签内容，如‘{5，14，16}’

 

 

select a.label,array_to_string(array_agg(b.name) ,',') as labelname 
from label as b join 
(select distinct  label as label from client) as a
on b.id= any(a.label)
group by a.label

 

 附：LABEL表建表SQL

DROP TABLE IF EXISTS "public"."label";
CREATE TABLE "public"."label" (
  "id" int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY (
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
),
  "code" varchar(20) COLLATE "pg_catalog"."default",
  "name" varchar(50) COLLATE "pg_catalog"."default",
  "parentid" int4,
  "comment" varchar(255) COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Primary Key structure for table label
-- ----------------------------
ALTER TABLE "public"."label" ADD CONSTRAINT "label_pkey" PRIMARY KEY ("id");