与unnest函数相反的Postgresql是什么,它可以从结果集中创建数组

我有一个部门-

So something like select dept_name, xxxxxxx from dept,emp where emp_dept_id = dept_id

返回

department1 | fred,bill,joe
department2 | faith, hope, charity
评论
  • kesse
    kesse 回复

    我会推荐数组:

    select d.dept_name, array_agg(e.name)
    from dept d join 
         emp e
         on e.emp_dept_id = d.dept_id
    group by d.dept_name;
    

    You can also use string_agg(e.name, ',') if you really prefer a string.

    笔记:

    • Never use commas in the FROM clause.
    • Always use proper, explicit, standard, readable JOIN syntax.
    • Qualify all column names in a query that references multiple tables.
    • Use table aliases so the query is easier to write and read.