Oracle function WMSYS. WM

1. Use the oracle function WMSYS. WM_CONCAT to convert the result to the result. Use the function WMSYS. WM_CONCAT. The query statement must be used together with groupbyselectaa, wmsys. wm_concat (t1.name) from (selectt. name, to_char (t. createdate, yyyy-mm-dd) aafromtd_usertw

1. Use the oracle function WMSYS. WM_CONCAT to convert the result to the result. Use the function WMSYS. WM_CONCAT. The query statement must be used with group by select aa and wmsys. wm_concat (t1.name) from (select t. name, to_char (t. createdate, 'yyyy-mm-dd') aa from td_user t w

1. Use of the oracle function WMSYS. WM_CONCAT

To convert the result to the result. Use the WMSYS. WM_CONCAT function.

The query statement must be used together with the group by statement.

Select aa, wmsys. wm_concat (t1.name) from (
Select t. name, to_char (t. createdate, 'yyyy-mm-dd') aa from td_user t where t. td_conference_id = 3218 and t. status = 1 and t. createdate> to_date ('1970-10-28 ', 'yyyy-mm-dd') order by createdate desc
) T1 group by t1.aa

:

:

2. Use of the mysql function GROUP_CONCAT

GROUP_CONCAT () is a function provided by the MySQL database. It is usually used together with GROUPBY. For details, refer to the MySQL official document: commit.

The complete syntax is as follows:

Group_concat ([DISTINCT] field to be connected [Order by asc/DESC sorting field] [Separator 'delimiter'])

Basic Query
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)

Group by id and print the value of the name field in one row, separated by commas (, default)
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)

Group by id, print the value of name field in one row, separated by semicolons
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)

Group by id and print the value of the redundant name field in one row,
Separated by commas

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)

Group by id, print the value of the name field in one row, and separate the values with commas (,). Sort the values by name in reverse order.
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)

Note:

A.int field connection trap

When using group_concat, note that if the connected field is of the int type, it must be converted to char and then combined,
Otherwise, after you execute the statement (ExecuteScalar or any other method that executes the SQL return result), the returned result will not be a comma-separated string,
Instead, it is byte [].

This problem cannot be found in SQLyog or other tools.

Select group_concat (ipaddress) from t_ip returns comma-separated strings
Select group_concat (id) from t_ip return byte []
Select group_concat (CAST (id as char) from t_dep returns comma-separated strings
Select group_concat (Convert (id, char) from t_dep returns comma-separated strings

Attached Cast and convert usage:
CAST (expr AS type), CONVERT (expr, type), CONVERT (expr USINGtranscoding_name)
CAST () and CONVERT () functions can be used to obtain values of one type and generate values of another type.

This type can be one of the following values:

BINARY [(N)]
CHAR [(N)]
DATE
DATETIME
DECIMAL
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

B. Length trap
After group_concat is used, it does not work if limit is used in select.
When using group_concat to connect a field, there is a limit on the length, not the number of connections. But you can set it.

Using group_concat_max_len system variables, you can set the maximum allowed length.
The syntax for this operation in the program is as follows, where val is an unsigned integer:
SET [SESSION | GLOBAL] group_concat_max_len = val;
If the maximum length has been set, the result is ended with the maximum length.

Run set global group_concat_max_len = 10 in SQLyog and re-open SQLyog. The setting will take effect.