postgresql - 在 Postgres 中连接行

Question

给定一个像这样的 Postgres 表

nummer  vorname    name        cash
------|-----------|-----------|-----
1       paul       smith       500
2       andy       london      700
2       mike       dover       700
3       clara      winchester  200

要查询此表，我的 sql 如下所示:

SELECT 
  nummer,
  vorname,
  name,  
  cash as total
FROM 
  data.myTable
GROUP BY
  nummer, 
  name,
  vorname,
  cash
ORDER BY
  nummer; 

是否可以连接 nummer 相同的两行(在本例中为 2)。意味着我的输出应该是这样的(如果数字相等，现金也将具有相同的值):

nummer  vorname    name        cash
------|-----------|-----------|-----
1       paul       smith       500
2       andy       london      700
        mike       dover       
3       clara      winchester  200

Answer 1

使用 GROUP BY 和聚合函数 string_agg() :

SELECT nummer
      ,string_agg(vorname, E'\n') AS vorname
      ,string_agg(name, E'\n') AS name
      ,cash
FROM   mytable
GROUP  BY nummer, cash
ORDER  BY nummer, cash;

我将 cash 添加到 GROUP BY 以获得原始值并防止相同的 nummer 不同的情况>.

关于您的评论:

is it possible to unique the query also by names. means if 2|andy london|700 is listed twice one should be removed.

SELECT nummer
      ,string_agg(vorname, E'\n') AS vorname
      ,string_agg(name, E'\n') AS name
      ,cash
FROM  (
   SELECT DISTINCT
          nummer, vorname, name, cash
   FROM   mytable
   ) AS m
GROUP  BY nummer, cash
ORDER  BY nummer, cash;