database - 根据id合并多个bytea行

Question

我正在使用一个 postgres 数据库，我需要根据 ID 将多行合并为一行。

ID  | A  | B  | C  |
--------------------
1   | x  |    |    |
1   | x  | y  |    |
2   | x  |    | z  |
3   |    | y  |    |
3   |    |    | z  |

A、B 和 C 是 bytea 列。

我需要按如下方式合并它:

ID  | A  | B  | C  |
--------------------
1   | x  | y  |    |
2   | x  |    | z  |
3   |    | y  | z  |

当我对 ID 执行 GROUP BY 时会出现问题，因为我无法为 bytea 列找到合适的聚合函数。

Answer 1

你总是可以用子查询来做

WITH allID as ( 
    SELECT distinct ID
    FROM YourTable
)
SELECT 
   ID, 
  (SELECT A FROM yourTable yt where yt.ID = ai.ID ORDER BY A LIMIT 1) as A,
  (SELECT B FROM yourTable yt where yt.ID = ai.ID ORDER BY B LIMIT 1) as B,
  (SELECT C FROM yourTable yt where yt.ID = ai.ID ORDER BY C LIMIT 1) as C
FROM allID as ai