如前所述，students表丢失了-没有它，就没有办法获取目标表中的第三行。如果我们包含它，这或多或少是一堆外部连接（因为不清楚哪些行可以丢失，哪些不能丢失）和一些聚合。
就像这样：
样本数据：

SQL> with
  2  stu (student_id) as
  3    (select 1 from dual union all
  4     select 2 from dual union all
  5     select 3 from dual
  6    ),
  7  cbs (class_id, student_id) as
  8    (select 1, 2 from dual union all
  9     select 2, 2 from dual union all
 10     select 3, 1 from dual
 11    ),
 12  fec (class_id, class_tuition_fee, class_item_fee) as
 13    (select 1, 100, 45   from dual union all
 14     select 2,  20, null from dual union all
 15     select 3,  30, 100  from dual
 16    ),
 17  iec (class_id, item_quant) as
 18    (select 1, 2    from dual union all
 19     select 2, null from dual union all
 20     select 3, 4    from dual
 21    )

字符串
查询：

22  select
 23    s.student_id,
 24    count(c.class_id) num_class,
 25    sum(nvl(f.class_tuition_fee, 0) + nvl(class_item_fee, 0)) total_fee,
 26    nvl(sum(i.item_quant), 0) as num_item
 27  from stu s left join cbs c on c.student_id = s.student_id
 28             left join fec f on f.class_id = c.class_id
 29             left join iec i on i.class_id = c.class_id
 30  group by s.student_id
 31  order by s.student_id;

STUDENT_ID  NUM_CLASS  TOTAL_FEE   NUM_ITEM
---------- ---------- ---------- ----------
         1          1        130          4
         2          2        165          2
         3          0          0          0

SQL>

型