复杂的左连接查询

一。查询A表的所有字段，还要查询和A表关联的B表的C字段的个数，条件是A.Fnum=B.Cnum

在上例中，学习人数和完成人数之前的字段都是A表的字段，学习人数和完成人数需要关联查询B表。

 

select FCOURSEWARE_NAME,fid ,fcourseware_type ,ftime_long ,flanguages ,fowner ,fcontributor ,fon_line_time,stu.sc,s.c,fstate

from COURSEWARE c left join (select count(1) as sc,course_number from user_course_study_info where course_number is not null group by course_number ) stu 

on stu.course_number = c.fcourse_id left join (select count(1) as c,course_number from user_course_study_info where BROWSE_SCORE=100 and course_number is not null group by course_number) s

on s.course_number = c.fcourse_id

 

二。 查A的记录时，同时要查询出和A关联的C表总记录数，C表不直接和A表关联，C表和B表是多对一，B和A是多对一。同时也要查出D表和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

 

查询结果如下：