PostgreSQL STRING_AGG

最新推荐文章于 2025-05-22 10:04:53 发布

spencer_tseng

最新推荐文章于 2025-05-22 10:04:53 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

分类专栏： database

本文链接：https://blog.csdn.net/spencer_tseng/article/details/83089409

database 专栏收录该内容

45 篇文章

订阅专栏

本文深入探讨了使用SQL进行复杂聚合查询的方法，通过具体案例展示了如何从多个表中联接、汇总和分组数据，特别关注于字符串聚合函数的使用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT
	t1.code,
    t1.name,
    t1.organ_id,
    t1.status,
    t1.remark,
    t1.create_ts,
    t1.create_user_id,
    t1.update_ts,
    t1.update_user_id,
    t1.corp_id,
    t1.union_id,
	t2.name AS organ_name,
	
	STRING_AGG(t4.name, '、') AS dept_name,
	STRING_AGG(t3.dept_id, ',') AS dept_id,
	
	STRING_AGG(t7.name, '、') AS user_name,
	STRING_AGG(t5.user_id, ',') AS user_id,
	STRING_AGG(t8.org_id, ',') AS organ_ids
FROM t_mis_cost_center t1 
LEFT JOIN t_mis_org t2 ON t1.organ_id = t2.id AND t2.org_type = '1'

LEFT JOIN t_mis_cost_center_dept_rel t3 on t1.id = t3.center_id
LEFT JOIN t_mis_org t4 ON t3.dept_id = t4.id AND t2.org_type = '2'

LEFT JOIN t_mis_cost_center_user_rel t5 on t1.id = t5.center_id
LEFT JOIN t_mis_user t6 ON t5.user_id = t6.id
LEFT JOIN t_mis_staff t7 ON t6.staff_id = t7.id
LEFT JOIN t_mis_staff_org_rel t8 ON t7.id = t8.staff_id

WHERE t1.id = '133388e98a5e40038855cf61751b8847'
GROUP BY 
	t1.code,
    t1.name,
    t1.organ_id,
    t1.status,
    t1.remark,
    t1.create_ts,
    t1.create_user_id,
    t1.update_ts,
    t1.update_user_id,
    t1.corp_id,
    t1.union_id,
    t2.name;