MySQL vs. Oracle：函数返回一个字符串结果，该结果由分组中的值连接组合而成

 1、MySQL 返回组内值连接组合

1.1 GROUP_CONCAT

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;

 或者

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

in MySQL, you can get the concatenated values of expression combinations. To eliminate duplicate values, use the DISTINCT clause. To sort values in the result, use the ORDER BY clause. To sort in reverse order, add the DESC (descending) keyword to the name of the column you are sorting by in the ORDER BY clause. The default is ascending order; this may be specified explicitly using the ASC keyword. The default separator between values in a group is comma (,). To specify a separator explicitly, use SEPARATOR followed by the string literal value that should be inserted between group values. To eliminate the separator altogether, specify SEPARATOR ''.

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer:

SET [GLOBAL | SESSION] group_concat_max_len = val;

The return value is a nonbinary or binary string, depending on whether the arguments are nonbinary or binary strings. The result type is TEXT or BLOB unless group_concat_max_len is less than or equal to 512, in which case the result type is VARCHAR or VARBINARY.

If GROUP_CONCAT() is invoked from within the mysql client, binary string results display using hexadecimal notation, depending on the value of the --binary-as-hex. For more information about that option, see Section 4.5.1, “mysql — The MySQL Command-Line Client”.

See also CONCAT() and CONCAT_WS(): Section 12.8, “String Functions and Operators”.

 1.2 CONCAT_WS()

 用法： CONCAT_WS(separator,str1,str2,...)

 CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.

 

mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');
        -> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');
        -> 'First name,Last Name'

2、Oracle 返回分组中值的连接组合

2.1 wm_concat

 oracle wm_concat

2.2 listagg

For a specified measure, LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column. 

 

即在每个分组内，LISTAGG根据order by子句对列植进行排序，将排序后的结果拼接起来。

measure_expr：可以是任何基于列的表达式。

delimiter：分隔符，默认为NUL

order_by_clause：order by子句决定了列值被拼接的顺序。

通过该用法，可以看出LISTAGG函数不仅可作为一个普通函数使用，也可作为分析函数。

 

 

SQL> select deptno,listagg(ename,',')within group(order by sal)name from emp group by deptno;

    DEPTNO NAME
---------- ----------------------------------------------------------------------------------------------------
    10 MILLER,CLARK,KING
    20 SMITH,ADAMS,JONES,FORD,SCOTT
    30 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE




SQL> select deptno,ename,sal,listagg(ename,',')within group(order by sal)over(partition by deptno)name from emp;

    DEPTNO ENAME             SAL NAME
---------- ---------- ---------- ----------------------------------------
        10 MILLER           1300 MILLER,CLARK,KING
        10 CLARK            2450 MILLER,CLARK,KING
        10 KING             5000 MILLER,CLARK,KING
        20 SMITH             800 SMITH,ADAMS,JONES,SCOTT,FORD
        20 ADAMS            1100 SMITH,ADAMS,JONES,SCOTT,FORD
        20 JONES            2975 SMITH,ADAMS,JONES,SCOTT,FORD
        20 SCOTT            3000 SMITH,ADAMS,JONES,SCOTT,FORD
        20 FORD             3000 SMITH,ADAMS,JONES,SCOTT,FORD
        30 JAMES             950 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
        30 MARTIN           1250 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
        30 WARD             1250 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
        30 TURNER           1500 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
        30 ALLEN            1600 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
        30 BLAKE            2850 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE

14 rows selected.