需求：查询出每一位“社工员”通过23门社工课进度100%的数量和23门社工课对应的考试通过的数量。

业务解析：

1.社工员——针对特定学员的一批人。在表USERS_SW_REGISTER中年份为2017的。

2.课程CourseWare.其中的EXAM_ID表示该门课程对应的考试，且唯一。

社工员的课程是在频道表JY_Channellesson的频道Id即Channelid在包括在23内的GetChannelByParentID(23)

另外由于课程重新指定考试，导致一门课程对应有两场考试。所以要包括之前的两场考试union select exam_id from chw_exam  where exam_id in(55,56)                                      

3.课程进度表USER_COURSE_REG,其中COMPELETE为进度，STATUS表示课程是否下架。

4.课程考试记录表CHW_EXAM_PREF,其中HIGH_SCORE为考试成绩，

5.该门考试的及格分是试卷表CHW_PAPER的试卷总分PAPER_SCORE_VALUE*考试表CHW_EXAM的PASS_SCORE/100

问题：

1.  客户最后统计的是考试要通过23门的人员名单，所以我们统计的最后只要判断考过的考试记录里面每个学员的记录有23条就通过了。

每人每场考试只有一条记录，

由于一门课程有两场考试，所以老考生在考完原来的23门考试之后再考新的试卷会导致超过23场考试记录。有的老考生会有大于23小于26条的记录。

不过只要判断他大于等于23条就算他通过了。

但是有一种情况会有问题：

如果老考生考过了没有重新指定的课程的所有考试，而且考过了一门课程重新制定的两场考试，也会显示考过23门考试。这个情况是不算他通过的。

解决的办法是如果在查出来的23门课程有一门课程下两场考试的就减去一门。不过这个在sql中太麻烦了，现在的sql语句已经达到50s了，再这么判断

估计要1分多钟了，还有一种方法是，在应用程序中用代码判断，应该是挺快的。

select c.user_id,c.USER_NAME,ug.USER_GROUP_NAME,
ISNULL(cp.CourseCount,0)as CourseCount,
ISNULL(pp.PassCount,0)as PassCount
from(
       select Top 100 percent a.user_id,b.USER_NAME ,b.USER_GROUP_ID
       from dbo.USERS_SW_REGISTER a ,users b
       where register_year=2017
       and a.user_id=b.USER_ID
     )
     as c
left join USER_GROUP ug on c.USER_GROUP_ID=ug.USER_GROUP_ID
left join (
    -- 取出23门章节测试都通过的学员
      select Top 100 percent u.USER_ID,u.USER_NAME,p.USER_GROUP_NAME,b.PassCount
      from (
              select USER_ID,COUNT(USER_ID) as PassCount
              from CHW_EXAM_PREF a
              where EXAM_ID in(
                                           --指定社工员考生相应的课程
                                            select exam_id
                                            from COURSEWARE
                                            where COURSE_ID in(
                                                                               select lessionid
                                                                               from JY_Channellesson
                                                                               where Channelid in (
                                                                                                              select ID from GetChannelByParentID(23)
                                                                                                               )
                                                                              )
                                           and STATUS=1 and exam_id is not null
                                           union
                                           select exam_id
                                           from chw_exam
                                           where exam_id in(55,56)
                                            )
       and CREATE_DATE<='2017-08-20 23:59:59'
       and HIGH_SCORE>=
       --一门课对应一场考试 取出相应的通过分数
                                      ((select pass_score from CHW_EXAM where EXAM_ID=a.EXAM_ID )*
                                      (select PAPER_SCORE_VALUE from CHW_PAPER where PAPER_ID =
                                      (select PAPER_ID from CHW_EXAM_PAPER where EXAM_ID=a.EXAM_ID))/100) --CHW_EXAM_PAPER是考试课程关联表

group by USER_ID
      ) as b,USERS u,USER_GROUP p
where
--b.PassCount>=23 and --23门课都通过
b.USER_ID=u.USER_ID --用户详细关联
and p.USER_GROUP_ID=u.USER_GROUP_ID --地区详细关联
order by u.USER_GROUP_ID
) as pp on c.user_id=pp.USER_ID

left join
(
     select * from (
     select USER_ID,COUNT(USER_ID)as CourseCount from USER_COURSE_REG
     where COURSE_ID in(
                                     select COURSE_ID
                                     from COURSEWARE
                                     where COURSE_ID in (
                                                                       select lessionid
                                                                       from JY_Channellesson
                                                                       where Channelid in (
                                                                                                      select ID from GetChannelByParentID(23)
                                                                                                       )
                                                                         )
                                    and STATUS=1 and exam_id is not null
                                        )
    and COMPLETE=100
    and IsDeleted=0
    group by USER_ID
) as a
--where a.CourseCount>=23
) as cp
on cp.USER_ID=c.user_id
order by c.USER_GROUP_ID