laravel - "'SELECT s.id AS 'Salarie.id', s.nom AS 'Salarie.nom', COALESCE(p' on line 1'附近的语法错误

Question

我有 3 张桌子，工资表salaries(id, name)和pointages table pointages(id, datep, salarie_id, sold)avances table avances(id, datea, montantA, salarie_id)。

表薪

Id  name
1   kamal   
2   imad  

积分表

id  datep       Salarie_id  sold
1   11/03/2020      1        120
2   05/03/2020      1        100
3   06/03/2020      2        50
4   07/03/2020      2        20

高级表

id  datea         montantA    Salarie_id
1   11/03/2020    120          1
2   02/03/2020    50           1

通过这 3 张表，我想恢复salaries.id和salaries.name和sum (pointages.sold)和sum (avances.montantA)这样

Salarie.id  Salaries.nom    Sum(sold)   Sum(montantA)
1              kamal           220          170
2              imad            70           0

我有一个 mysql 请求允许恢复正确的数据和总和，我想用 laravel（查询引擎）编写这个函数，但我不知道如何将我的请求直接放在etatq1u function控制器中，但它给了我这个错误：Erreur de syntaxe près de "'SELECT s.id AS 'Salarie.id', s.nom AS 'Salarie.nom', COALESCE(p' à la ligne 1 ... 薪水控制器.php

public function etatq1u(request $request){
$dataP = DB::select('"SELECT 
            s.id AS Salarie.id, 
            s.nom AS Salarie.nom, 
            COALESCE(p.somme, 0) AS SUM(sold), 
             COALESCE(a.somme, 0) AS SUM(montantA 
            FROM 
            Salaries s 
             LEFT JOIN (SELECT SUM(sold) AS somme, salarie_id FROM pointages GROUP BY salarie_id) AS p ON p.salarie_id = s.id
             LEFT JOIN (SELECT SUM(montantA) AS somme, salarie_id FROM avances GROUP BY salarie_id) AS a ON a.salarie_id = s.id
            GROUP BY s.id, p.salarie_id, a.salarie_id"');
            return response($dataP);
}

标签： laravel