Oracle兼容-函数-WM_CONCAT()函数

1. 语法

  1. WM_CONCAT([DISTINCT] expr,... [ORDER BY col [ASC|DESC],...]
  2.     [SEPARATOR str_const])

2. 定义和用法

WM_CONCAT(expr) 函数的作用是从 expr 中连接所有非NULL的字符串。如果没有非NULL的字符串，那么它就会返回NULL。

用法：WM_CONCAT([DISTINCt] 要连接的字段列表 [ORDER BY 排序字段 ASC|DESC ] [SEPARATOR '分隔符'])。

3. Oracle兼容说明

  • WM_CONCAT() 是一个聚合函数，在Oracle 10g推出，在10g版本中，返回字符串类型，在11g版本中返回clob类型，在12c后已取消该函数。在Oracle中，该函数是一个undocumented function（未公开函数）），Oracle官方不推荐使用的函数。
  • 从网络公开资料中并未看到Oracle的 WM_CONCAT() 函数支持 ORDER BY 子句。而GreatSQL的 WM_CONCAT() 函数是支持 ORDER BY 子句的。
  • 在Windowing（窗口函数用法）环境中，OVER() 内的 ORDER BY 子句，与 WM_CONCAT() 中的 ORDER BY 子句不可同时出现。
  • 当 WM_CONCAT() 中没有 ORDER BY 子句时，允许在 OVER() 内使用 ORDER BY 子句；当 OVER() 中使用 ORDER BY 子句时，会将 ORDER BY 视为 PARTITION 的一部分；WM_CONCAT() 内使用 ORDER BY 时则是单纯的将结果排序。

4. 示例

  1. greatsql> CREATE TABLE t1 (
  2. grp INT,
  3. a BIGINT UNSIGNED,
  4. c CHAR(10) NOT NULL,
  5. d CHAR(10) NOT NULL
  6. );
  8. greatsql> INSERT INTO t1 VALUES (1,1,"a","a"),
  9. (2,2,"b","a"),
  10. (2,3,"c","b"),
  11. (3,4,"E","a"),
  12. (3,5,"C","b");
  14. greatsql> SELECT * FROM t1;
  15. +------+------+---+---+
  16. | grp  | a    | c | d |
  17. +------+------+---+---+
  18. |    1 |    1 | a | a |
  19. |    2 |    2 | b | a |
  20. |    2 |    3 | c | b |
  21. |    3 |    4 | E | a |
  22. |    3 |    5 | C | b |
  23. +------+------+---+---+
  25. greatsql> SELECT WM_CONCAT(c) FROM t1;
  26. +--------------+
  27. | WM_CONCAT(c) |
  28. +--------------+
  29. | a,b,c,E,C    |
  30. +--------------+
  32. greatsql> SELECT REPLACE(WM_CONCAT(c),',','|') FROM t1;
  33. +-------------------------------+
  34. | REPLACE(WM_CONCAT(c),',','|') |
  35. +-------------------------------+
  36. | a|b|c|E|C                     |
  37. +-------------------------------+
  39. greatsql> SELECT grp, WM_CONCAT(c) c FROM t1 GROUP BY grp;
  40. +------+------+
  41. | grp  | c    |
  42. +------+------+
  43. |    1 | a    |
  44. |    2 | b,c  |
  45. |    3 | E,C  |
  46. +------+------+
  48. greatsql> SELECT grp, WM_CONCAT(DISTINCT c ORDER BY c DESC) FROM t1 GROUP BY grp;
  49. +------+---------------------------------------+
  50. | grp  | WM_CONCAT(DISTINCT c ORDER BY c DESC) |
  51. +------+---------------------------------------+
  52. |    1 | a                                     |
  53. |    2 | c,b                                   |
  54. |    3 | E,C                                   |
  55. +------+---------------------------------------+
  57. greatsql> SELECT grp, WM_CONCAT(DISTINCT c ORDER BY c DESC) OVER (PARTITION BY grp) FROM t1;
  58. +------+---------------------------------------------------------------+
  59. | grp  | WM_CONCAT(DISTINCT c ORDER BY c DESC) OVER (PARTITION BY grp) |
  60. +------+---------------------------------------------------------------+
  61. |    1 | a                                                             |
  62. |    2 | c,b                                                           |
  63. |    2 | c,b                                                           |
  64. |    3 | E,C                                                           |
  65. |    3 | E,C                                                           |
  66. +------+---------------------------------------------------------------+
  68. greatsql> SELECT grp, WM_CONCAT(DISTINCT c) OVER (PARTITION BY grp ORDER BY c DESC) FROM t1;
  69. +------+---------------------------------------------------------------+
  70. | grp  | WM_CONCAT(DISTINCT c) OVER (PARTITION BY grp ORDER BY c DESC) |
  71. +------+---------------------------------------------------------------+
  72. |    1 | a                                                             |
  73. |    2 | c                                                             |
  74. |    2 | c,b                                                           |
  75. |    3 | E                                                             |
  76. |    3 | E,C                                                           |
  77. +------+---------------------------------------------------------------+
  79. greatsql> EXPLAIN SELECT grp, WM_CONCAT(DISTINCT c ORDER BY c DESC) OVER (PARTITION BY grp) FROM t1;
  80. greatsql> SHOW WARNINGS\G
  81. *************************** 1. row ***************************
  82.   Level: Note
  83.    Code: 3598
  84. Message: To get information about window functions use EXPLAIN FORMAT=JSON
  85. *************************** 2. row ***************************
  86.   Level: Note
  87.    Code: 1003
  88. Message: /* select#1 */ select `greatsql`.`t1`.`grp` AS `grp`,wm_concat(distinct `greatsql`.`t1`.`c` order by `greatsql`.`t1`.`c` desc separator ',') OVER (PARTITION BY `greatsql`.`t1`.`grp` )  AS `WM_CONCAT(DISTINCT c ORDER BY c DESC) OVER (PARTITION BY grp)` from `greatsql`.`t1`
  90. greatsql> EXPLAIN SELECT grp, WM_CONCAT(DISTINCT c) OVER (PARTITION BY grp ORDER BY c DESC) FROM t1;
  91. greatsql> SHOW WARNINGS\G
  92. *************************** 1. row ***************************
  93.   Level: Note
  94.    Code: 3598
  95. Message: To get information about window functions use EXPLAIN FORMAT=JSON
  96. *************************** 2. row ***************************
  97.   Level: Note
  98.    Code: 1003
  99. Message: /* select#1 */ select `greatsql`.`t1`.`grp` AS `grp`,wm_concat(distinct `greatsql`.`t1`.`c` separator ',') OVER (PARTITION BY `greatsql`.`t1`.`grp` ORDER BY `greatsql`.`t1`.`c` desc )  AS `WM_CONCAT(DISTINCT c) OVER (PARTITION BY grp ORDER BY c DESC)` from `greatsql`.`t1`

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx