当前位置：网站首页>MySQL keyword group_ Concat, combined connection query

MySQL keyword group_ Concat, combined connection query

2022-04-23 03:16:00 【Xiaodaoxian 97】

One 、

Try to think about what to do if there is such a need :
There is a student list , And a score sheet . To find out the name and score of each student , Fractions are separated by commas
eg： Zhang San 80,90,100

This demand is very simple , There are many ways to solve , The simplest thing is that we can query the students first , Then check the score , Finally, it is assembled by program code .

Student list (stu)

id name
1 Zhang San
2 Li Si
3 Wang Wu

Score table (course)

id name stu_id score
1 Chinese language and literature 1 80
2 mathematics 1 90
3 English 1 75
4 Chinese language and literature 2 55
5 mathematics 2 45
6 English 3 90

Two 、

But some time ago, a friend recommended MySql keyword group_concat, It's easy to use this keyword .

SELECT
	s.name name,
	GROUP_CONCAT(c.score) score
FROM
stu s 
LEFT JOIN course c ON s.id = c.stu_id
GROUP BY s.id

Insert picture description here


3、 ... and 、

If we want to query the account name 、 fraction , And divided by semicolon, how should I write ？

SELECT
	s.name name,
	GROUP_CONCAT(c.name, ':',c.score  separator '; ') score
FROM
stu s 
LEFT JOIN course c ON s.id = c.stu_id
GROUP BY s.id

Insert picture description here

stay group_concat The fields inside , You can't do anything null, Otherwise, the whole will be null

版权声明
本文为[Xiaodaoxian 97]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/04/202204230315096690.html

边栏推荐

猜你喜欢

随机推荐