工作摘记一（求必修课的活动数量以及已完成的必修课活动数量）

题：

求必修课的活动数量以及已完成的必修课活动数量

数据库的数据字典：

lcms_termcourse_task   章表｛主键：task_id｝

lcms_termcourse_task_chpt  章与节的关联表（它是中间表）｛没主键，但有task_id和chapter_id｝

lcms_course_chapterinfo 节的基本信息表｛主键：chapter_id，上级ID：PARENT_ID，层级：LAYER_TYPE，是否必修：IS_MUST｝

lcms_termcourse_act  活动表｛主键：ACT_ID，章ID：TASK_ID，章节ID：CHAPTER_ID｝

问题描述：

由于每一节点都可以设置其为必修课，而此章为一个树形结构，将不知道这必修课会设置到那一层里面。因此之前一直认为要遍历该章所有的活动，然后根据每一个活动去搜索其父节点，看是否已被设置为必修，若直接父级为非必修，则往上继续搜索，一直找到根为止。如果一直找到根都没有找到IS_MUST为1的话，那么就标志此活动为非必修课的活动。如果是这样的话，一句SQL语句是搞不掂，（我想：非必要时，不写数据库函数或者存储过程。那就是能用SQL查询的，就用SQL来查询），而且效率上也有很大问题。

经过思考，我认为分为两种情况，1、搜索第一层的节点，若第一层为必修课的，就不再搜索此节点的下层。因此一旦搜索到，就标明此层下面所有子节点的活动都为必修课。2、若第一层为非必修课的，则重复此操作（向下搜索）。但这样还是要分两步走，一条SQL还是完不成我想要的。

第一步的SQL如下：

 select t2.act_id
       from (select c.*
               from lcms_course_chapterinfo c
              start with c.chapter_id in
                         (select b.chapter_id
                            from lcms_termcourse_task      a,
                                 lcms_termcourse_task_chpt b,
                                 lcms_course_chapterinfo   c
                           where a.task_id = b.task_id
                             and b.chapter_id = c.chapter_id
                             and a.task_id = ?
                             and c.layer_type = '1'
                             and c.is_must = '1'
                             and a.isdeleted = 'N' and b.isdeleted = 'N' and c.isdeleted = 'N')
             connect by prior c.chapter_id = c.parent_id) t1,--求出第一层必修下的所有章ID
            (select * from lcms_termcourse_act t where t.task_id = ?
              and t.isdeleted = 'N' ) t2--求出该章下的所有活动
      where t1.chapter_id = t2.chapter_id;

那么第二步该如何写SQL呢？想了想，觉得还是不妥呀。

从第一步中再仔细分析发现：

既然可以查询出第一层的已必修的节点，那么该章下所有被标明为必修的节点都应该能被查询出来呀。查询出来后，再搜索它下层的活动不就行了。

OK，开始修改SQL：

被标明为必修的所有节点：

                select c.chapter_id
                        from lcms_course_chapterinfo c
                       where c.is_must = '1'
                       start with c.chapter_id in
                                  (select b.chapter_id
                                     from lcms_termcourse_task      a,
                                          lcms_termcourse_task_chpt b,
                                          lcms_course_chapterinfo   c
                                    where a.task_id = b.task_id
                                      and b.chapter_id = c.chapter_id
                                      and a.task_id =
                                          '2f16cf45ac10a58d000c157e603a5549'
                                      and c.layer_type = '1'
                                      and a.isdeleted = 'N'
                                      and b.isdeleted = 'N'
                                      and c.isdeleted = 'N')
                      connect by prior c.chapter_id = c.parent_id

然后搜索其下层活动：

select c.*
           from lcms_course_chapterinfo c
          start with c.chapter_id in
                     (select c.chapter_id
                        from lcms_course_chapterinfo c
                       where c.is_must = '1'
                       start with c.chapter_id in
                                  (select b.chapter_id
                                     from lcms_termcourse_task      a,
                                          lcms_termcourse_task_chpt b,
                                          lcms_course_chapterinfo   c
                                    where a.task_id = b.task_id
                                      and b.chapter_id = c.chapter_id
                                      and a.task_id =
                                          '2f16cf45ac10a58d000c157e603a5549'
                                      and c.layer_type = '1'
                                      and a.isdeleted = 'N'
                                      and b.isdeleted = 'N'
                                      and c.isdeleted = 'N')
                      connect by prior c.chapter_id = c.parent_id)
         connect by prior c.chapter_id = c.parent_id

最后关联活动表，查询出必修的活动：

 select distinct t2.act_id
   from (select c.*
           from lcms_course_chapterinfo c
          start with c.chapter_id in
                     (select c.chapter_id
                        from lcms_course_chapterinfo c
                       where c.is_must = '1'
                       start with c.chapter_id in
                                  (select b.chapter_id
                                     from lcms_termcourse_task      a,
                                          lcms_termcourse_task_chpt b,
                                          lcms_course_chapterinfo   c
                                    where a.task_id = b.task_id
                                      and b.chapter_id = c.chapter_id
                                      and a.task_id =
                                          '2f16cf45ac10a58d000c157e603a5549'
                                      and c.layer_type = '1'
                                      and a.isdeleted = 'N'
                                      and b.isdeleted = 'N'
                                      and c.isdeleted = 'N')
                      connect by prior c.chapter_id = c.parent_id)
         connect by prior c.chapter_id = c.parent_id) t1, --求出第一层必修下的所有章ID
        (select *
           from lcms_termcourse_act t
          where t.task_id = '2f16cf45ac10a58d000c157e603a5549'
            and t.isdeleted = 'N') t2 --求出该章下的所有活动
  where t1.chapter_id = t2.chapter_id

这样，就大功告成了，其实这里使用了distinct。把相同的活动过滤掉。

至于已完成 的必修课的活动，也很简单了。这样就不描述了。