1. oracle : wm_concat作用:将列转换为一行并以逗号分隔;
SELECT *
FROM (
SELECT a.group_id, a.group_name, a.group_sort, a.create_time, wm_concat(b.members_name) AS membersName
FROM t_oa_pgroup a
LEFT JOIN t_oa_pgroupmembers b
ON a.group_id = b.group_id
WHERE a.create_user_id = 'f60b9ca0-092e-470a-a6ad-cc71de7371be' AND a.group_id != 'f60b9ca0-092e-470a-a6ad-cc71de7371be'
GROUP BY a.group_id, a.group_name, a.group_sort, a.create_time
ORDER BY a.group_sort, a.create_time ) TOTAL;
2. postgreSql: string_agg(字段,',') 将列转换为一行并以逗号分隔;
SELECT *
FROM (
SELECT a.group_id, a.group_name, a.group_sort, a.create_time, string_agg(b.members_name,',') AS membersName
FROM t_oa_pgroup a
LEFT JOIN t_oa_pgroupmembers b
ON a.group_id = b.group_id
WHERE a.create_user_id = 'f60b9ca0-092e-470a-a6ad-cc71de7371be' AND a.group_id != 'f60b9ca0-092e-470a-a6ad-cc71de7371be'
GROUP BY a.group_id, a.group_name, a.group_sort, a.create_time
ORDER BY a.group_sort, a.create_time ) TOTAL;
oracle postgreSql sql将列转换为一行并以逗号分隔;
最新推荐文章于 2024-07-30 10:23:55 发布