A表和B表是一对多,B表和C是一对多,A和D是一对多,要查询出C和A关联的记录数,和D对应A的记录数。
select * from (select t.fid,
t.fcourse_code,
t.fcourse_name,
t.funit,
t.flevel,
t.FCREATETIME,
sum(arr.tc) as tc,
sum(choose.tc2) as tc2
from course_info t left join (select count(1) as tc,
fid,
fcourse_id
from teach_arrangement
group by fid, fcourse_id) arr on arr.fcourse_id = t.fid left join (select count(1) as tc2,
FTEACH_ARRANGEMENT_ID
from TEACHPLAN_STUDENT_CHOOSE
group by FTEACH_ARRANGEMENT_ID) choose on choose.FTEACH_ARRANGEMENT_ID = arr.fid
group by t.fid,
t.fcourse_code,
t.fcourse_name,
t.funit,
t.flevel,
t.FCREATETIME) temp left join (select count(1) as tc3,
fcourse_id
from courseware
group by fcourse_id) ware on ware.fcourse_id = temp.fid
查询结果如下 :