標籤：des style color 使用 ar div 問題 sp on
mysql concat(str1,str2...)串連兩個字串，（數字也是可以的，會轉成字串）
MySQL的concat函數在連接字串的時候，只要其中一個是NULL,那麼將返回NULL
mysql> select concat(‘11‘,‘22‘,null);
+------------------------+
| concat(‘11‘,‘22‘,null) |
+------------------------+
| NULL |
+------------------------+
1 row in set (0.00 sec)concat_ws()函數, 表示concat with separator,即有分隔字元的字串串連
如串連後以逗號分隔
mysql> select concat_ws(‘,‘,‘11‘,‘22‘,‘33‘);
+-------------------------------+
| concat_ws(‘,‘,‘11‘,‘22‘,‘33‘) |
+-------------------------------+
| 11,22,33 |
+-------------------------------+
1 row in set (0.00 sec)現在有這樣一個需求，要尋找使用者名稱和密碼為(user1,pwd1),(user2,pwd2),(user2,plwd3)由於欄位與欄位之間有關聯，我們就可以使用串連解決這個問題：select * from user where concat(username,‘-‘,pwd) in (‘user1-pwd1‘,‘user2-pwd2‘) group_concat()可用來行轉列, Oracle沒有這樣的函數
完整的文法如下
group_concat([DISTINCT] 要串連的欄位[Order BY ASC/DESC 排序欄位] [Separator ‘分隔字元‘])
如下例子
mysql> select * from aa;
+------+------+
| id | name |
+------+------+
| 1 | 10 |
| 1 | 20 |
| 1 | 20 |
| 2 | 20 |
| 3 | 200 |
| 3 | 500 |
+------+------+
6 rows in set (0.00 sec)
3.1 以id分組，把name欄位的值列印在一行，逗號分隔(預設)
mysql> select id,group_concat(name) from aa group by id;
+------+--------------------+
| id | group_concat(name) |
+------+--------------------+
| 1 | 10,20,20 |
| 2 | 20 |
| 3 | 200,500 |
+------+--------------------+
3 rows in set (0.00 sec)
3.2 以id分組，把name欄位的值列印在一行，分號分隔
mysql> select id,group_concat(name separator ‘;‘) from aa group by id;
+------+----------------------------------+
| id | group_concat(name separator ‘;‘) |
+------+----------------------------------+
| 1 | 10;20;20 |
| 2 | 20 |
| 3 | 200;500 |
+------+----------------------------------+
3 rows in set (0.00 sec)
3.3 以id分組，把去冗餘的name欄位的值列印在一行，逗號分隔
mysql> select id,group_concat(distinct name) from aa group by id;
+------+-----------------------------+
| id | group_concat(distinct name) |
+------+-----------------------------+
| 1 | 10,20 |
| 2 | 20 |
| 3 | 200,500 |
+------+-----------------------------+
3 rows in set (0.00 sec)
3.4 以id分組，把name欄位的值列印在一行，逗號分隔,以name排倒序
mysql> select id,group_concat(name order by name desc) from aa group by id;
+------+---------------------------------------+
| id | group_concat(name order by name desc) |
+------+---------------------------------------+
| 1 | 20,20,10 |
| 2 | 20 |
| 3 | 500,200 |
+------+---------------------------------------+
3 rows in set (0.00 sec)
4、repeat()函數，用來複製字串,如下‘ab‘表示要複製的字串，2表示複製的份數
mysql> select repeat(‘ab‘,2);
+----------------+
| repeat(‘ab‘,2) |
+----------------+
| abab |
+----------------+
1 row in set (0.00 sec)
又如
mysql> select repeat(‘a‘,2);
+---------------+
| repeat(‘a‘,2) |
+---------------+
| aa |
+---------------+
1 row in set (0.00 sec)
mysql concat和group_concat
