sql - Vertica SQL 中的 Concat GROUP BY

Question

我需要获得一个逗号分隔的 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 语句解决这个问题？

Answer 1

您必须使用 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;

结果

姓名 | ID
-------+-------
格雷格 | 1,3,4
保罗 | 2,5