数据库服务器环境

数据库	版本
sql>mysql	5.7.34
PostgreSQL	10.10

原表结构和数据

students

id	name	gender	age
1	张三	男	33
2	李四	男	25
3	小谢	女	25
4	小周	女	26

scores

id	score	student_id	course
1	67	1	英语
2	78	1	数学
3	88	2	英语
4	97	2	数学
5	76	3	英语

sql_29">pgsql

SQL

sql">select students.*, array_to_json( array_agg (row_to_json(scores))) AS JsonArray
from students
left join scores on students.id = scores.student_id
group by students.id

另一种更简便的写法

sql">select students.*, jsonb_agg( distinct scores) AS JsonArray
from students
left join scores on students.id = scores.student_id
group by students.id

结果

id	name	gender	age	jsonarray
4	小周	女	26	[null]
2	李四	男	25	[{“id”:3,“score”:88,“student_id”:2,“course”:“英语”},{“id”:4,“score”:97,“student_id”:2,“course”:“数学”}]
3	小谢	女	25	[{“id”:5,“score”:76,“student_id”:3,“course”:“英语”}]
1	张三	男	33	[{“id”:1,“score”:67,“student_id”:1,“course”:“英语”},{“id”:2,“score”:78,“student_id”:1,“course”:“数学”}]

---

sql>mysql_59">sql>mysql

SQL

sql">SELECT students.*,
       JSON_ARRAYAGG(JSON_OBJECT('id', scores.id,
                                'score', scores.score,
                                'course', scores.course,
                                'student_id', scores.student_id)) AS JsonArray
FROM students
         left join scores on students.id = scores.student_id
GROUP BY students.id;

结果

id	name	gender	age	JsonArray
1	张三	男	33	[{“id”: 1, “score”: 67, “course”: “英语”, “student_id”: 1}, {“id”: 2, “score”: 78, “course”: “数学”, “student_id”: 1}]
2	李四	男	25	[{“id”: 3, “score”: 88, “course”: “英语”, “student_id”: 2}, {“id”: 4, “score”: 97, “course”: “数学”, “student_id”: 2}]
3	小谢	女	25	[{“id”: 5, “score”: 76, “course”: “英语”, “student_id”: 3}]
4	小周	女	26	[{“id”: null, “score”: null, “course”: null, “student_id”: null}]

---

相关文章导读

sql>mysql 在select查询语句中使用临时变量累计求和 ；
相同列值的记录中再根据条件取其中最大或最新一条；
sql>mysql经纬度求距离并排序

sql>mysql pgsql 实现多行记录合并成一行 分组合并 用指定字符做分割