PostgreSQL如何查询每个班级对应的学生姓名？

数据表结构与测试数据 #

首先创建并填充测试用的数据表：

create table students (id int, name text);
insert into students values 
(1,'AA'),
(2,'BB'),
(3,'CC'),
(4,'DD');

create table classes (name text,students text[]);
insert into classes values
('CL-1','{2,4}'),
('YL-2','{2,1,4}'),
('CL-3','{2,3}'),
('BL-33','{2}'),
('CL-5','{1,3,4}'),
('CL-6','{4}');

需求说明 #

查询每个班级对应的学生姓名，最终输出格式要求将学生姓名以逗号分隔（例如AA,BB,CC）。

原尝试SQL的问题 #

你写的SQL存在几处错误：

• 表名拼写错误：student应为students，class应为classes
• 字段名错误：st.names应为st.name，cl.student应为cl.students
• 数组无法直接用IN匹配，PostgreSQL需用= ANY()操作符处理数组元素
• 缺少聚合函数，无法将多个学生姓名拼接成字符串

可行解法 #

方法一：展开数组后关联聚合

通过unnest将班级的学生ID数组拆分为单行数据，关联学生表后聚合拼接姓名：

select 
    cl.name as class_name,
    string_agg(st.name, ',') as student_names
from classes cl
cross join unnest(cl.students) as student_id
join students st on st.id = student_id::int
group by cl.name
order by cl.name;

方法二：子查询+= ANY()匹配数组

直接在子查询中用= ANY()匹配数组内的学生ID，再聚合拼接：

select 
    cl.name as class_name,
    (select string_agg(st.name, ',') 
     from students st 
     where st.id = ANY(cl.students::int[])) as student_names
from classes cl
order by cl.name;

方法三：修正原SQL后的版本

基于你最初的思路，修正所有错误后得到的可用SQL：

select 
    cl.name as class_name,
    (select string_agg(st.name, ',') 
     from students st 
     where st.id = ANY(cl.students::int[])) as student_names
from classes cl;

内容的提问来源于stack exchange，提问作者user881703