mysql - Laravel:将结果与 UnionAll 与计数相结合

Question

假设我在表 resume_profiles 中有 2 列

   current_location      city
    | Chennai  |    | Kolkatta   |
    | Mumbai   |    | Ahmaedabad |
    | Pune     |    | Kolkatta   |
    | Kolkatta |    | Pune       |

我需要将这些结果组合成一个 SET，所以我会有这样的东西:

   City        Aggregate
| Chennai    |    | 1 |
| Mumbai     |    | 1 |
| Pune       |    | 2 |
| Kolkatta   |    | 3 |
| Ahmaedabad |    | 1 |

查询:

$current_locations = ResumeProfile::selectRaw('current_location as city');

ResumeProfile::selectRaw('city,count(*) as aggregate')
           ->unionAll($current_locations)
           ->groupBy('city')->get();

当我使用上面的查询时，我得到了下面的异常查询

SQLSTATE[21000]:基数违规:1222 使用的 SELECT 语句具有不同数量的列(SQL:(select city,count(*) as aggregate from resume_profiles group by city) union all(从 resume_profiles 中选择 current_location 作为城市))

我不知道如何实现这个

Answer 1

尝试这样做:

  $current_locations = ResumeProfile::selectRaw('current_location as city');

 $subquery=ResumeProfile::selectRaw('city')
       ->unionAll($current_locations);

   DB::table(DB::raw("({$subquery->toSql()}) AS s"))
         ->selectRaw('s.city,count(*) as aggregate')
          ->groupBy('s.city')->get();