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

767人阅读 评论(0)

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

 select t2.act_id
from (select c.*
from lcms_course_chapterinfo c
(select b.chapter_id
lcms_course_chapterinfo   c
and b.chapter_id = c.chapter_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;

OK，开始修改SQL：

                select c.chapter_id
from lcms_course_chapterinfo c
where c.is_must = '1'
(select b.chapter_id
lcms_course_chapterinfo   c
and b.chapter_id = c.chapter_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
(select c.chapter_id
from lcms_course_chapterinfo c
where c.is_must = '1'
(select b.chapter_id
lcms_course_chapterinfo   c
and b.chapter_id = c.chapter_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
(select c.chapter_id
from lcms_course_chapterinfo c
where c.is_must = '1'
(select b.chapter_id
lcms_course_chapterinfo   c
and b.chapter_id = c.chapter_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
and t.isdeleted = 'N') t2 --求出该章下的所有活动
where t1.chapter_id = t2.chapter_id

0
0
相关文章推荐

* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
个人资料
• 访问：123218次
• 积分：2053
• 等级：
• 排名：第19759名
• 原创：60篇
• 转载：24篇
• 译文：0篇
• 评论：41条
评论排行
最新评论