1.使用GROUP_CONCAT函数
|
1 |
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.更换连接方式
|
1 |
//重点：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.同时查询多个字段
|
1 |
//重点：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 |
效果：
2021，过得真快。2022祝你好运
微信扫一扫打赏
支付宝扫一扫打赏
