問了好多人,都不知道group_concat這個(gè)函數(shù)。
?
這個(gè)函數(shù)好啊,能將相同的行組合起來,省老事了。
?
MySQL中g(shù)roup_concat函數(shù)
完整的語法如下:
group_concat([DISTINCT] 要連接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
?
基本查詢
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字段的值打印在一行,逗號(hào)分隔(默認(rèn))
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字段的值打印在一行,分號(hào)分隔
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字段的值打印在一行,
逗號(hào)分隔
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字段的值打印在一行,逗號(hào)分隔,以name排倒序
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)
?
測(cè)試sql,項(xiàng)目中用到的。
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
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號(hào)聯(lián)系: 360901061
您的支持是博主寫作最大的動(dòng)力,如果您喜歡我的文章,感覺我的文章對(duì)您有幫助,請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長非常感激您!手機(jī)微信長按不能支付解決辦法:請(qǐng)將微信支付二維碼保存到相冊(cè),切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
