php - Laravel 使用 sum 和多连接查询

Question

伙计们，我尝试使用连接查询从 mysql 获取数据，但我需要对我的 rate 列求和，在我尝试执行查询后出现错误。

我的查询是这样的：

 $data['companies'] = db::table('companies')
    ->leftjoin('company_rate', 'companies.id', '=', 'company_rate.company_id')
    ->select('companies.*', DB::raw('SUM(company_rate.rate) as company_rates'))
    ->groupBy('companies.id', 'company_rate.rate') 
    ->get();
    return view('allcompanies', $data);

错误代码是这样的：

SQLSTATE[42000]: Syntax error or access violation: 1055 'expresslog.companies.created_at' isn't in GROUP BY (SQL: select `companies`.*, SUM(company_rate.rate) as company_rates from `companies` left join `company_rate` on `companies`.`id` = `company_rate`.`company_id` group by `companies`.`id`, `company_rate`.`rate`)

标签： phpmysqllaravel

Answer 1

这段代码现在可以正常工作了:)

$data['companies'] = db::table('companies')
            ->leftjoin('company_rate', 'companies.id', '=', 'company_rate.company_id')
            ->select('companies.id', 'companies.company_name', 'companies.company_mol', 'companies.company_eik', 'companies.company_dds_number', 'companies.company_phone', 'companies.company_email', 'companies.company_adress', 'companies.company_agent_phone', 'companies.company_agent_email', DB::raw('SUM(company_rate.rate) as company_rates'))
            ->groupBy('companies.id', 'companies.company_name', 'companies.company_mol', 'companies.company_eik', 'companies.company_dds_number', 'companies.company_phone', 'companies.company_email', 'companies.company_adress', 'companies.company_agent_phone', 'companies.company_agent_email') 
            ->get();