sql查询：查询所有学生的个人信息和加入的多个班级(group_concat)

最新推荐文章于 2022-10-10 09:43:35 发布

php的绯闻女友

最新推荐文章于 2022-10-10 09:43:35 发布

阅读量4.9k

点赞数

分类专栏： MySQL 文章标签： sql 数据库 database group_concat

本文链接：https://blog.csdn.net/Moxi099/article/details/122099357

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1.使用GROUP_CONCAT函数

SELECT
	ls.stu_name,
     ls.id,
	ls.user_id,
	GROUP_CONCAT(class_name)
FROM
	lx_stu AS ls
	LEFT JOIN lx_stu_class AS lsc ON ls.id = lsc.stu_id
	LEFT JOIN lx_class as lc ON lsc.class_id = lc.id
GROUP BY ls.id

此时学生的加入的班级名称会以逗号的形式组成一个字符串

效果：

2.更换连接方式

//重点：separator ";"

SELECT
	ls.stu_name,
 ls.id,
	ls.user_id,
class_name,
	GROUP_CONCAT(class_name separator ";")
	
FROM
	lx_stu AS ls
	LEFT JOIN lx_stu_class AS lsc ON ls.id = lsc.stu_id
	LEFT JOIN lx_class as lc ON lsc.class_id = lc.id
GROUP BY ls.id

效果：

3.同时查询多个字段

//重点：GROUP_CONCAT(distinct class_name,"->",lsc.id separator ";") as class_name

SELECT
	ls.stu_name,
	ls.id,
	ls.user_id,
	GROUP_CONCAT(distinct class_name,"->",lsc.id separator ";") as class_name
	
FROM
	lx_stu AS ls
	LEFT JOIN lx_stu_class AS lsc ON ls.id = lsc.stu_id
	LEFT JOIN lx_class as lc ON lsc.class_id = lc.id
GROUP BY ls.id

效果：