1.concat?
concat()函數是將多個字符串組合在一起，形成一個大的字符串；如果連接的字符串中存在一個為NULL，則輸出的結果為NULL，語法格式為：
concat(str1,str2,....strn)
-- 1、字符之間不加連接符
mysql> select concat("01","趙雷","男");
+-----------------------------+
| concat("01","趙雷","男") |
+-----------------------------+
| 01趙雷男 |
+-----------------------------+
1 row in set (0.00 sec)-- 2、字符之間添加連接符
mysql> select concat("01-","趙雷-","男");
+-------------------------------+
| concat("01-","趙雷-","男") |
+-------------------------------+
| 01-趙雷-男 |
+-------------------------------+
1 row in set (0.00 sec)-- 3、忽略空字符串
mysql> mysql> select concat("01","趙雷","","男");
+--------------------------------+
| concat("01","趙雷","","男") |
+--------------------------------+
| 01趙雷男 |
+--------------------------------+
1 row in set (0.00 sec)-- 4、存在NULL的情況
mysql> select concat("01","趙雷",NULL,"男"); -- 結果直接顯示為NULL
+----------------------------------+
| concat("01","趙雷",NULL,"男") |
+----------------------------------+
| NULL |
+----------------------------------+
1 row in set (0.01 sec)上面的NULL是MySQL中NULL，如果NULL本身就是字符串，則結果不相同：
mysql> select concat("01","趙雷","NULL","男");
+------------------------------------+
| concat("01","趙雷","NULL","男") |
+------------------------------------+
| 01趙雷NULL男 |
+------------------------------------+
1 row in set (0.01 sec)2.concat_ws
concat_ws()函數相比較于concat()多了一個指定的連接符號，語法為：
concat_ws(separator, str1, str2, str3)
-- 1、指定不同的連接符號：分別指定逗號和加號mysql> select concat_ws(",","01","趙雷","男");
+------------------------------------+
| concat_ws(",","01","趙雷","男") |
+------------------------------------+
| 01,趙雷,男 |
+------------------------------------+
1 row in set (0.00 sec)mysql> select concat_ws("+","01","趙雷","男");
+------------------------------------+
| concat_ws("+","01","趙雷","男") |
+------------------------------------+
| 01+趙雷+男 |
+------------------------------------+
1 row in set (0.00 sec)-- 2、不忽略空字符串
mysql> select concat_ws("+","01","趙雷","","男");
+---------------------------------------+
| concat_ws("+","01","趙雷","","男") |
+---------------------------------------+
| 01+趙雷++男 |
+---------------------------------------+
1 row in set (0.00 sec)-- 3、忽略NULL；不管幾個NULL都會忽略
mysql> select concat_ws("+","01","趙雷",NULL,"男");
+-----------------------------------------+
| concat_ws("+","01","趙雷",NULL,"男") |
+-----------------------------------------+
| 01+趙雷+男 |
+-----------------------------------------+
1 row in set (0.00 sec)-- 忽略兩個NULL
mysql> select concat_ws("+","01",NULL,"趙雷",NULL,"男");
+----------------------------------------------+
| concat_ws("+","01",NULL,"趙雷",NULL,"男") |
+----------------------------------------------+
| 01+趙雷+男 |
+----------------------------------------------+
1 row in set (0.00 sec)3.group_concat
group：分組的意思；concat：連接。合起來就是分組連接，具體語法為：
GROUP_CONCAT(DISTINCT expression ORDER BY expression SEPARATOR sep);DISTINCT子句用于在連接分組之前消除組中的重復值ORDER BY連接之前按升序或者降序排列。默認是升序SEPARATOR指定在組中的值之間插入的文字值。如果不指定分隔符，則GROUP_CONCAT函數使用逗號(，)作為默認分隔符- 函數會自動忽略NULL值，如果所有的參數都是NULL，則結果返回NULL
GROUP_CONCAT函數返回二進制或非二進制字符串，取決于參數。 默認情況下，返回字符串的最大長度為1024。通過在SESSION或GLOBAL級別設置group_concat_max_len系統變量來擴展最大長度。
set session group_concat_max_len=18783847439738273; -- 防止超出范圍數據被截掉-- 1、將每個學生的成績單獨列出來
mysql> select s_id, group_concat(s_score) from Score group by s_id;
+------+-----------------------+
| s_id | group_concat(s_score) |
+------+-----------------------+
| 01 | 80,90,96 |
| 02 | 70,60,80 |
| 03 | 80,81,85 |
| 04 | 50,40,30 |
| 05 | 76,87 |
| 06 | 43,56 |
| 07 | 89,94 |
+------+-----------------------+
7 rows in set (0.01 sec)-- 2、指定連接符+
mysql> select s_id, group_concat(s_score separator "+") from Score group by s_id;
+------+-------------------------------------+
| s_id | group_concat(s_score separator "+") |
+------+-------------------------------------+
| 01 | 80+90+96 |
| 02 | 70+60+80 |
| 03 | 80+81+85 |
| 04 | 50+40+30 |
| 05 | 76+87 |
| 06 | 43+56 |
| 07 | 89+94 |
+------+-------------------------------------+
7 rows in set (0.00 sec)-- 3、指定排序的字段
-- 分數s_score已經完成了排序（指定了降序）；上面的結果不指定則默認是降序
mysql> select s_id, group_concat(distinct s_score order by s_score desc separator "+") from Score group by s_id;
+------+--------------------------------------------------------------------+
| s_id | group_concat(distinct s_score order by s_score desc separator "+") |
+------+--------------------------------------------------------------------+
| 01 | 96+90+80 |
| 02 | 80+70+60 |
| 03 | 85+81+80 |
| 04 | 50+40+30 |
| 05 | 87+76 |
| 06 | 56+43 |
| 07 | 94+89 |
+------+--------------------------------------------------------------------+
7 rows in set (0.00 sec)-- 4、去重操作
-- distinct s_score表示對分數去重，取出每個學生的不同分數（表中每個學生的分數都不相同，結果同上）
mysql> select s_id, group_concat(distinct s_score order by s_score desc separator "+") from Score group by s_id;
+------+--------------------------------------------------------------------+
| s_id | group_concat(distinct s_score order by s_score desc separator "+") |
+------+--------------------------------------------------------------------+
| 01 | 96+90+80 |
| 02 | 80+70+60 |
| 03 | 85+81+80 |
| 04 | 50+40+30 |
| 05 | 87+76 |
| 06 | 56+43 |
| 07 | 94+89 |
+------+--------------------------------------------------------------------+
7 rows in set (0.00 sec)
)
)
