第一种,
concat(str1,str2,str3.......)将多个字符串连接成一个字符串;
SELECT
*,
CONCAT(UserName, Subject1, Score)
studentScores_Test
注:当有任何一个参数为null时,则返回值为null
添加逗号实例:SELECT
*, CONCAT(UserName, ',',Subject1,',', Score)
studentScores_Test
第二种,
concat_ws(分隔符,str1,str2,........),和concat()一样,将多个字符串连接成一个字符串,但是可以一次性指定分隔符~,但分隔符不能为空。
以逗号进行分割:
SELECT
*,
CONCAT_WS( ',',UserName,Subject1, Score)
studentScores_Test
2.以UserName分组,把Subject1,Score连接起来;
SELECT
UserName,
GROUP_CONCAT(Subject1,Score)
studentScores_Test
GROUP BY
UserName
3.以UserName分组,把Subject1连接起来,以Subject1排序;
SELECT
UserName,
GROUP_CONCAT(Subject1 ORDER BY Subject1 DESC)
studentScores_Test
GROUP BY UserName
3.以UserName分组,把Subject1连接起来,以Subject1排序,连接符号是“--”;
SELECT
UserName,
GROUP_CONCAT(Subject1 ORDER BY Subject1 DESC SEPARATOR '---')
studentScores_Test
GROUP BY UserName
3.以UserName分组,把Subject1连接起来并去重,以Subject1排序,连接符号是“*”;
SELECT
UserName,
GROUP_CONCAT(DISTINCT Subject1 ORDER BY Subject1 DESC SEPARATOR '*')
studentScores_Test
GROUP BY UserName