MySQL GROUP_CONCAT函式 將多行資料合併成一行

神諭丶發表於2015-09-16
MySql函式
實際場景需求是這樣的：

某網站的後臺管理，新建一個使用者時，需要指派給其一個角色，該角色具有一定許可權：
比如，“銷售專員”這個角色有A,B,C的許可權，“銷售經理”這個角色有A,B,C,E的許可權，“財務”這個角色有B,C,D,E,F的許可權。
（直接可以理解為ORACLE資料庫中的“角色-許可權”）

開發的同學需求是知道某個角色，獲取其全部的許可權，並在一條row中顯示。

測試表建立：

  1. CREATE TABLE `a` (
  2.   `id` int(11) DEFAULT NULL,
  3.   `name` char(1) DEFAULT NULL
  4. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  1. INSERT INTO a SELECT 1,'A';
  2. INSERT INTO a SELECT 1,'B';
  3. INSERT INTO a SELECT 1,'C';
  4. INSERT INTO a SELECT 2,'A';
  5. INSERT INTO a SELECT 2,'B';
即：
  1. mysql> SELECT * FROM a;
  2. +------+------+
  3. | id   | name |
  4. +------+------+
  5. | 1    | A    |
  6. | 1    | B    |
  7. | 1    | C    |
  8. | 2    | A    |
  9. | 2    | B    |
  10. +------+------+
  11. 5 rows in set (0.00 sec)
要求顯示為：

  1. +------+---------+
  2. | id   | name    |
  3. +------+---------+
  4. | 1    | C,A,A,B |
  5. | 2    | A,B     |
  6. +------+---------+

這裡就需要用到GROUP_CONCAT函式
官方給出的簡介是：
GROUP_CONCAT()
 Return a concatenated string

給出的語法和例子是：

This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values. The full syntax is as follows: 

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

mysql> SELECT student_name, 
    -> GROUP_CONCAT(test_score) 
    -> FROM student 
    -> GROUP BY student_name;

Or: 

mysql> SELECT student_name, 
    -> GROUP_CONCAT(DISTINCT test_score 
    -> ORDER BY test_score DESC SEPARATOR ' ') 
    -> FROM student 
    -> GROUP BY student_name;

簡單來說，可以通過：

  1. mysql> SELECT id , GROUP_CONCAT(name) name
  2.     -> FROM a 
  3.     -> GROUP BY id;
來獲得這樣的結果集：

  1. +------+---------+
  2. | id   | name    |
  3. +------+---------+
  4. | 1    | A,A,B,C |
  5. | 2    | A,B     |
  6. +------+---------+

當然也可以對其的分隔符進行修改：
  1. mysql> SELECT id, GROUP_CONCAT(name SEPARATOR ';')
  2.     -> FROM a
  3.     -> GROUP BY id;
這將使分隔符改為" ; "

同樣也可以對name列進行排序：

  1. mysql> SELECT id, GROUP_CONCAT(name ORDER BY name DESC) name
  2.     -> FROM a
  3.     -> GROUP BY id;
  4. +------+---------+
  5. | id   | name    |
  6. +------+---------+
  7. | 1    | C,B,A,A |
  8. | 2    | B,A     |
  9. +------+---------+
  10. 2 rows in set (0.00 sec)

當然官方還給出了可以通過DISTINCT來排除冗餘
用法如下：

  1. mysql> SELECT id, GROUP_CONCAT(DISTINCT name) name
  2.     -> FROM a
  3.     -> GROUP BY id;
  4. +------+-------+
  5. | id   | name  |
  6. +------+-------+
  7. | 1    | A,B,C |
  8. | 2    | A,B   |
  9. +------+-------+
  10. 2 rows in set (0.00 sec)




作者公眾號（持續更新）




來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-1800845/，如需轉載，請註明出處，否則將追究法律責任。

相關文章