MySQL中group_concat函數-和group by配合使用

標籤：

MySQL中group_concat函數完整的文法如下：group_concat([DISTINCT] 要串連的欄位 [Order BY ASC/DESC 排序欄位] [Separator ‘分隔字元‘]) 基本查詢 Sql代碼  收藏代碼select * from aa;  +------+------+| id| name |+------+------+|1 | 10||1 | 20||1 | 20||2 | 20||3 | 200 ||3 | 500 |+------+------+6 rows in set (0.00 sec) 以id分組，把name欄位的值列印在一行，逗號分隔(預設) Sql代碼  收藏代碼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) 以id分組，把name欄位的值列印在一行，分號分隔 Java代碼  收藏代碼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) 以id分組，把去冗餘的name欄位的值列印在一行，逗號分隔 Sql代碼  收藏代碼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) 以id分組，把name欄位的值列印在一行，逗號分隔，以name排倒序 Sql代碼  收藏代碼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) 測試sql，項目中用到的。Sql代碼  收藏代碼SELECT          EMPLOYEES.EMPID          ,EMPLOYEES.EMPNAME          ,DEPARTMENTS.DEPARTMENTNAME          ,EMPLOYEES.DEPTID          ,EMPLOYEES.EMPPWD          ,EMPLOYEES.INSIDEEMAIL          ,EMPLOYEES.OUTSIDEEMAIL          ,EMPLOYEES.DELEFLAG          ,EMPLOYEES.EMPCLASS          ,(CONCAT(‘[‘, <span style="color: #ff0000;">GROUP_CONCAT</span>  (ROLE.Role_Name SEPARATOR ‘],[‘), ‘]‘)) AS ROLENAME          ,(concat( ‘[‘, (              SELECT                      <span style="color: #ff0000;">GROUP_CONCAT</span>  (DEPARTMENTS.DEPARTMENTNAME separator ‘],[‘)                  FROM                      EMP_ROLE_DEPT                          LEFT JOIN DEPARTMENTS                              ON (                                  DEPARTMENTS.DEPARTMENTID = EMP_ROLE_DEPT.DEPTID                                  AND DEPARTMENTS.DELEFLAG = 0                              )                  GROUP BY                      EMP_ROLE_DEPT.EMPID                  HAVING                      EMP_ROLE_DEPT.EMPID = EMPLOYEES.EMPID          ),‘]‘)) AS DEPARTMENTRIGHT      FROM          EMPLOYEES              LEFT JOIN DEPARTMENTS                  ON (                      DEPARTMENTS.DEPARTMENTID = EMPLOYEES.DEPTID                      AND DEPARTMENTS.DELEFLAG = 0                  )              LEFT JOIN ROLE_EMP                  ON (ROLE_EMP.EMP_ID = EMPLOYEES.EMPID)              LEFT JOIN ROLE                  ON (ROLE_EMP.ROLE_ID = ROLE.ROLE_ID)  <span style="color: #ff0000;">    GROUP BY          EMPLOYEES.EMPID</span>        HAVING          EMPLOYEES.EMPID LIKE ‘%%‘          AND EMPLOYEES.EMPNAME LIKE ‘%%‘          AND EMPLOYEES.DELEFLAG = 0          AND (              EMPLOYEES.EMPCLASS = ‘1‘              OR EMPLOYEES.EMPCLASS = ‘2‘          )          AND EMPLOYEES.DEPTID = ‘001‘ LIMIT 0          ,16   

 

MySQL中group_concat函數-和group by配合使用