sql case when 使用

   select st.Number,st.Name,st.ClassCode, 

   isnull(com_stu.cd,0) as cd,isnull(com_stu.zt,0) as zt, 

   isnull(com_stu.kk,0) as kk,isnull(com_stu.zywwc,0)as zywwc 

   from t_student st left join (

   select Studentid, 

   count(case when  Type = '01' then 1 end) as cd, 

   count(case when  Type = '02' then 1 end) as zt, 

    count(case when  Type = '03' then 1 end) as kk, 

    count(case when  Type = '04' then 1 end) as zywwc 

    from t_comments_stu 

    where 1=1 

   and CourseID='" + strCourse + "' 

    and CONVERT(varchar(12) , ClassTime, 112 )='" + Common.GetDateTime(strClassTime, "yyyyMMdd") + "' 

   group by StudentID 

   ) com_stu on st.ID = com_stu.StudentID  

  where st.ClassCode='" + strClass + "'