我需要获取一个逗号分隔的 id 列表作为混乱的第三方 API 的字段:s 这是我试图实现的目标的简化版本。
| id | name |
|====|======|
| 01 | greg |
| 02 | paul |
| 03 | greg |
| 04 | greg |
| 05 | paul |
SELECT name, {some concentration function} AS ids
FROM table
GROUP BY name
返回
| name | ids |
|======|============|
| greg | 01, 03, 04 |
| paul | 02, 05 |
我知道MySQL具有CONCAT_GROUP功能,由于环境原因,我希望在不安装更多功能的情况下解决此问题。也许我可以使用 OVER 语句来解决这个问题?
OVER()
与NVL()
一起使用(您必须将串联扩展到每个名称的 10 个以上实例):
CREATE TABLE t1 (
id int,
name varchar(10)
);
INSERT INTO t1
SELECT 1 AS id, 'greg' AS name
UNION ALL
SELECT 2, 'paul'
UNION ALL
SELECT 3, 'greg'
UNION ALL
SELECT 4, 'greg'
UNION ALL
SELECT 5, 'paul';
COMMIT;
SELECT name,
MAX(DECODE(row_number, 1, a.id)) ||
NVL(MAX(DECODE(row_number, 2, ',' || a.id)), '') ||
NVL(MAX(DECODE(row_number, 3, ',' || a.id)), '') ||
NVL(MAX(DECODE(row_number, 4, ',' || a.id)), '') ||
NVL(MAX(DECODE(row_number, 5, ',' || a.id)), '') ||
NVL(MAX(DECODE(row_number, 6, ',' || a.id)), '') ||
NVL(MAX(DECODE(row_number, 7, ',' || a.id)), '') ||
NVL(MAX(DECODE(row_number, 8, ',' || a.id)), '') ||
NVL(MAX(DECODE(row_number, 9, ',' || a.id)), '') ||
NVL(MAX(DECODE(row_number, 10, ',' || a.id)), '') id
FROM
(SELECT name, id, ROW_NUMBER() OVER(PARTITION BY name ORDER BY id) row_number FROM t1) a
GROUP BY a.name
ORDER BY a.name;
结果
姓名 | 编号------+------- 格雷格 |1,3,4 保罗 |2,5
看看 vertica 安装附带的 vertica 示例中的 Concatenate UDAF这是MySQL的等价物。您可以直接安装它。
more/opt/vertica/sdk/examples/AggregateFunctions/Concatenate.cpp
-- Shell comppile
cd /opt/vertica/sdk/examples/AggregateFunctions/
g++ -D HAVE_LONG_INT_64 -I /opt/vertica/sdk/include -Wall -shared -Wno-unused-value
-fPIC -o Concatenate.so Concatenate.cpp /opt/vertica/sdk/include/Vertica.cpp
-- Create LIBRARY
CREATE LIBRARY AggregateFunctionsConcatenate AS '/opt/vertica/sdk/examples/AggregateFunctions/Concatenate.so';
CREATE AGGREGATE FUNCTION agg_group_concat AS LANGUAGE 'C++' NAME 'ConcatenateFactory' LIBRARY AggregateFunctionsConcatenate;
in the Concatenate.cpp
replace : input_len*10
with : 65000
您必须在代码中替换此值的两个位置。
65000 是您可以使用 varchar 获得的最大长度。 由于 Vertica 不会将 65000 全部用于小于 65000 个字符的值,因此您可以
最简单的方法是使用在 github 上找到的官方 Vertica UDF 之一 https://github.com/vertica/Vertica-Extension-Packages/tree/master/strings_package 它提供了一个group_concat功能。安装过程可在自述文件中找到,甚至还提供了示例。
一个非常古老的问题,但作为更新,您可以使用 listagg() 函数
01 | greg |
02 | 保罗 |
03 | greg |
04 | greg |
05 | 保罗 |