数据展示
select "id" ,space_id from iot_device d where d."id" in ('6_43','6_44') ;
select "id","name" from iot_space where "id" IN
(select regexp_split_to_table(space_id,',') FROM iot_device where "id" in ('6_43','6_44') );
SELECT
d.device_id,
string_agg ( d.splicing || '', ',' ) space_id,
string_agg ( s."name" || '', '/' ) "name"
FROM
iot_space s
RIGHT JOIN ( SELECT "id" device_id, regexp_split_to_table( space_id, ',' ) splicing FROM iot_device WHERE "id" IN ( '6_43', '6_44' ) ) d ON d.splicing = s."id"
GROUP BY
d.device_id
string_agg( ‘要组合的字段’ || ‘’, ‘组合的字符’ ) 组合一列数据函数
regexp_split_to_table(拆分字段,‘符号’) 拆分数据为一列