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