Group_concat function in MySQL

The complete syntax is as follows:

Group_concat ([DISTINCT] field to connect [order by Asc/desc sort field] [Separator ' delimiter '])

Basic Query

SQL code

1. SELECT * from AA;

+------+------+
| id| name |
+------+------+
| 10|
| 20|
| 20|
| 20|
|3 | 200 |
|3 | 500 |
+------+------+
6 rows in Set (0.00 sec)

Group by ID, print the value of the Name field on one line, comma separated (default)

SQL code

1. Select Id,group_concat (name) from the AA GROUP by ID;

+------+--------------------+
| id| Group_concat (name) |
+------+--------------------+
| 10,20,20|
| 20 |
|3 | 200,500|
+------+--------------------+
3 Rows in Set (0.00 sec)

Group by ID, print the value of the Name field on one line, semicolon delimited

Java code

1. Select Id,group_concat (name separator '; ') from the AA group by ID;

+------+----------------------------------+
| id| Group_concat (name separator '; ') |
+------+----------------------------------+
| 10;20;20 |
| 20|
|3 | 200;500 |
+------+----------------------------------+
3 Rows in Set (0.00 sec)

Group by ID, print the value of the redundant Name field to a line,

Comma delimited

SQL code

1. Select Id,group_concat (distinct name) from the AA GROUP by ID;

+------+-----------------------------+
| id| Group_concat (distinct name) |
+------+-----------------------------+
| 10,20|
| 20 |
|3 | 200,500 |
+------+-----------------------------+
3 Rows in Set (0.00 sec)

Group by ID, print the value of the Name field in one line, comma separated, and reverse by name

SQL code

1. Select Id,group_concat (name order by name desc) from the AA GROUP by ID;

+------+---------------------------------------+
| id| Group_concat (name order BY name Desc) |
+------+---------------------------------------+
| 20,20,10 |
| 20|
|3 | 500,200|
+------+---------------------------------------+
3 Rows in Set (0.00 sec)

Test SQL, used in the project.

SQL code

2. SELECT
4. EMPLOYEES. EMPID
6. , EMPLOYEES. EmpName
8. , departments. Departmentname
10. , EMPLOYEES. DeptID
12. , EMPLOYEES. Emppwd
14. , EMPLOYEES. Insideemail
16. , EMPLOYEES. Outsideemail
18. , EMPLOYEES. Deleflag
20. , EMPLOYEES. Empclass
22. , (CONCAT (' [', <span style="color: #ff0000;" >GROUP_CONCAT</span>
24. (ROLE. Role_name SEPARATOR '],['), '] ') as ROLENAME
26. , (Concat ( ' ['), (
28. SELECT
30. <span style="color: #ff0000;" >GROUP_CONCAT</span>
32. (Departments. Departmentname separator '],[')
34. from
36. Emp_role_dept
38. left JOIN departments
40. On (
42. Departments. DepartmentID = emp_role_dept. DeptID
44. and departments. Deleflag = 0
46. )
48. GROUP by
50. Emp_role_dept. EMPID
52. having
54. Emp_role_dept. EMPID = EMPLOYEES. EMPID
56. ),'] ') as departmentright
58. from
60. EMPLOYEES
62. left JOIN departments
64. On (
66. Departments. DepartmentID = EMPLOYEES. DeptID
68. and departments. Deleflag = 0
70. )
72. left JOIN role_emp
74. On (role_emp. emp_id = EMPLOYEES. EMPID)
76. left JOIN ROLE
78. On (role_emp. role_id = ROLE. ROLE_ID)
80. <span style="color: #ff0000;" > GROUP by
82. EMPLOYEES. Empid</span>
86. having
88. EMPLOYEES. EMPID like '% '
90. and EMPLOYEES. EmpName like '% '
92. and EMPLOYEES. Deleflag = 0
94. and (
96. EMPLOYEES. Empclass = ' 1 '
98. OR EMPLOYEES. Empclass = ' 2 '
100. )
102. and EMPLOYEES. DeptID = ' 001 ' LIMIT 0
104. , 16

Group_concat function in MySQL