String_agg用于多列 [英] String_agg for multiple columns

问题描述

我有以下选择

$repairs = DB::select(DB::raw('
            select r.id repair_id, w.name, w.surname
                from repairs r
                    join repair_worker rw on r.id = rw.repair_id
                    join workers w on w.id = rw.worker_id
                where r.vehicle_id = ?
                group by w.name, w.surname, r.id
           '),[$vehicle->id]);

我正在尝试获取 w.name 和 w.surname 放在一个字段中，例如用逗号分隔。有一个功能 string_agg（column，’，’），但是我没有运气让它正常工作。我总是会收到语法错误。

and I am trying to get w.name and w.surname into one field, separated by a comma for example. There is a function string_agg(column, ',') but I am having no luck to get it working. I always get syntax error.

我的尝试：

$repairs = DB::select(DB::raw('
                select r.id repair_id, string_agg(w.name, w.surname, ', ') workers
                    from repairs r
                        join repair_worker rw on r.id = rw.repair_id
                        join workers w on w.id = rw.worker_id
                    where r.vehicle_id = ?
                    group by r.id
          '),[$vehicle->id]);

您会怎么做？

我正在使用最新的Postgresql 9 +

I am using the newest Postgresql 9+

推荐答案

使用 ||

$repairs = DB::select(DB::raw("
    select r.id repair_id, string_agg(w.name || ' ' || w.surname, ', ') workers
    from repairs r
        join repair_worker rw on r.id = rw.repair_id
        join workers w on w.id = rw.worker_id
    where r.vehicle_id = ?
    group by r.id
    "),[$vehicle->id]);

这篇关于String_agg用于多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！