我如何在Laravel ORM中做到这一点?
SELECT
s.id,
s.name AS NAME,
COALESCE(su.cnt, 0) AS SUBJECTs,
COALESCE(e.marks, 0) AS MARKS
FROM student s
LEFT JOIN
(
SELECT student_id, COUNT(*) AS cnt
FROM subject
GROUP BY student_id
) su
ON s.id = su.student_id
LEFT JOIN
(
SELECT student_id, SUM(mark) AS marks
FROM exam
GROUP BY student_id
) e
ON s.id = e.student_id;
我想把学生和科目、学生和考试结合起来。当我加入第三个表时,问题就发生了,它复制了结果。
发布于 2019-04-01 01:21:28
这是
DB::table('student as su')->selectRaw("
su.id,
su.name,
COALESCE(su.cnt, 0) as SUBJECTS,
COALESCE(e.marks, 0) as MARKS
")->leftJoin(DB::raw("(
SELECT student_id, COUNT(*) AS cnt
FROM subject
GROUP BY student_id
) su"), 's.id', '=', 'su.student_id')
->leftJoin(DB::raw("(
SELECT student_id, SUM(mark) AS marks
FROM exam
GROUP BY student_id
) e"), 's.id', '=', 'e.student_id')
https://stackoverflow.com/questions/55443146
复制相似问题