如何在Laravel中编写子查询 原生SQL转Laravel查询求助

实现方法有两种，你可以按需选择： #

方法1：使用Laravel查询构造器（推荐，自动防SQL注入）

// 先在文件顶部引入DB门面
use Illuminate\Support\Facades\DB;

$id = 实际传入的services_pos_id参数;
$total_sum = DB::table(function ($query) use ($id) {
    $query->from('services_p_o_items_management')
        ->selectRaw('*, qty * cost as total_price')
        ->where('services_pos_id', $id);
}, 'A')
->sum('total_price');

如果需要显式指定别名total_sum，可以调整写法：

use Illuminate\Support\Facades\DB;

$id = 实际传入的services_pos_id参数;
$total_sum = DB::table(function ($query) use ($id) {
    $query->from('services_p_o_items_management')
        ->selectRaw('*, qty * cost as total_price')
        ->where('services_pos_id', $id);
}, 'A')
->selectRaw('SUM(total_price) as total_sum')
->value('total_sum');

方法2：使用原生查询

use Illuminate\Support\Facades\DB;

$id = 实际传入的services_pos_id参数;
$result = DB::selectOne(
    'SELECT SUM(A.total_price) AS total_sum 
    FROM (
        SELECT *, (qty * cost) AS total_price 
        FROM services_p_o_items_management 
        WHERE services_pos_id = ?
    ) AS A',
    [$id]
);
$total_sum = $result->total_sum;

内容的提问来源于stack exchange，提问作者AliKhan