database - 根据ID合并多个bytea行

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

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

A、B和C是副塔。
我需要合并如下：

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

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

最佳答案

您始终可以使用子查询来完成此操作

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

关于database - 根据ID合并多个bytea行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48214765/