带有别名的联接中的Laravel Eloquent子查询

KevinB 发表于 Dev

凯文布

我喜欢从第一个表中选择所有信息，并从第二个表中的特定列中计算它们之间的相互关系。

这是SQL查询

Select * from emp_informations e inner
join(Select SUM(latein) as totallate, enrollnum from dtrs where latein > 0 
and (date_in between 2015-03-01 and 2015-03-31) and (date_out between 2015-
03-01 and 2015-03-31)  group by enrollnum)dtrs on dtrs.enrollnum=e.EmpID 
group by EmpID

此查询的laravel代码是什么？

$employees=DB::select(DB::raw('Select * from emp_informations e join (Select 
SUM(latein) as totallate, enrollnum from dtrs where latein > 0 and date_in 
between 2015-03-01 and 2015-03-31 and date_out between 2015-03-01 and 2015-
03-31  group by enrollnum)dtrs on dtrs.enrollnum=e.EmpID group by EmpID '));

但是什么都没显示。

先感谢您。

凯文布

我已经解决了我的问题，这是代码。

$employees = DB::table('emp_informations')
    ->join(DB::raw("(Select SUM(latein) as totallate, enrollnum from dtrs 
    where latein > 0 and (date_in between '2015-03-01' and '2015-03-31' and 
    date_out between '2015-03-01' and '2015-03-31') group by 
    enrollnum)dtrs"), function($join)
    {
        $join->on('emp_informations.EmpID', '=', 'dtrs.enrollnum');
    })
    ->groupBy('EmpID')
    ->get();

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。