你好,来自所有国家。我需要将SQL查询转换为LARAVEL 4.2,因为我还没有管理它的经验,我自己的!
SQL 在 SQL 服务器 Microsoft 2008 中
SELECT TOP(10) logst_data, LogSt_date, LogSt_studid
FROM Home_LogStudents
WHERE logst_action = 225
AND LogSt_sectionID = 257
AND LogSt_date IN
(SELECT MIN(LogSt_date)
FROM Home_LogStudents
WHERE logst_data <> '0'
AND logst_action = 225
AND LogSt_sectionID = 257
AND (LogSt_studid IN
(select home_id
FROM Hw_StudentClasses
INNER JOIN Home_Students
ON home_studid = Stclass_studid
WHERE Stclass_classid = (129)))
GROUP BY LogSt_studid)
ORDER BY LogSt_data DESC
我所能做的就是在下一张图片中:在此处输入图像描述,但$id和 $class_id 不是在$query中可识别....还有代码:
$users = DB::Table('Home_LogStudents')
->select('LogSt_data','LogSt_date','LogSt_studid')
->where('LogSt_action','=',225)
->where('LogSt_sectionID','=',$id)
->whereIn('LogSt_date',function($query){
$query->select('LogSt_date')
->from('Home_LogStudents')
->where('LogSt_data','!=',0)
->where('LogSt_action','=',225)
->where('LogSt_sectionID','=',$id)
->whereIn('LogSt_studid',function($query){
$query->select('home_id')
->from('Hw_StudentClasses')
->join('Home_Students','Home_Students.home_studid','=','Hw_StudentClasses.Stclass_studid')
->where('Stclass_classid','=',$class_id);
}
)
->groupBy('LogSt_studid');
})
->orderBy('LogSt_data','DESC')
->take(10)
->get();
提前感谢您,只为看!
我没有测试它,但是您需要将参数$id, $class_id
传递到匿名函数中。
$users = DB::Table('Home_LogStudents')
->select('LogSt_data','LogSt_date','LogSt_studid')
->where('LogSt_action','=', 225)
->where('LogSt_sectionID','=', $id)
->whereIn('LogSt_date', function($query) use($id, $class_id) {
return $query->select('LogSt_date')
->from('Home_LogStudents')
->where('LogSt_data','!=', 0)
->where('LogSt_action','=', 225)
->where('LogSt_sectionID','=', $id)
->whereIn('LogSt_studid', function($query) use($class_id){
return $query->select('home_id')
->from('Hw_StudentClasses')
->join('Home_Students','Home_Students.home_studid','=','Hw_StudentClasses.Stclass_studid')
->where('Stclass_classid','=', $class_id);
}
)
->groupBy('LogSt_studid');
})
->orderBy('LogSt_data','DESC')
->take(10)
->get();
找到了答案,这很容易,所以我所做的只是使用 Raw Query,因为查询太复杂了,无法将其转换为 laravel,或者也许我做不到,所以我这样做了.....
$users = DB::select('SELECT TOP (10) Home_LogStudents.LogSt_data, Home_LogStudents.LogSt_date, Home_LogStudents.LogSt_studid, Home_Students.home_firstname, Home_Students.home_lastname
FROM Home_LogStudents
INNER JOIN Home_Students
ON Home_LogStudents.LogSt_studid = Home_Students.home_id
WHERE (Home_LogStudents.LogSt_action = 225)
AND (Home_LogStudents.LogSt_sectionID = '.$id.')
AND (Home_LogStudents.LogSt_date IN
(SELECT MIN(LogSt_date) AS Expr1
FROM Home_LogStudents AS Home_LogStudents_1
WHERE (LogSt_data <> ''0'')
AND (LogSt_action = 225)
AND (LogSt_sectionID = '.$id.') AND (LogSt_studid IN
(SELECT Home_Students_1.home_id
FROM Hw_StudentClasses INNER JOIN
Home_Students AS Home_Students_1
ON Home_Students_1.home_studid = Hw_StudentClasses.Stclass_studid
WHERE (Hw_StudentClasses.Stclass_classid = ' .$class_id.')))
GROUP BY LogSt_studid))
ORDER BY Home_LogStudents.LogSt_data DESC
');
实际上,我在字符串之外使用了 $id 和 $class_id 使用 '. $id.和".$class_id"。
我希望这对您有所帮助,所以对于复杂的查询...只需使用原始查询