SQL Server case when 日期字符串转换 多表查询 嵌套子查询

select distinct stu.*,dbo.GetClassNameByStudentCode(stu.Code) as ClassName,dbo.GetCourseNameByStudentCode(stu.Code) as CourseName,dbo.GetLinkmanByStudentId(stu.Id) as Linkman,dbo.GetContactByStudentId(stu.Id) as Contact,case when svr.Linkman is not NULL then svr.Linkman else dbo.GetLinkmanByStudentId(stu.Id) end as VisitLinkman,case when svr.Contact is not NULL then svr.Contact else dbo.GetContactByStudentId(stu.Id) end as VisitContact,case when u1.Sname is not NULL and u1.Sname<>'' then u1.Sname else u1.Username end as VisitFollowUserName,CONVERT(CHAR(10), svr.FollowTime, 23) as VisitLastFollowTime,scl.Name as SchoolAreaName,svr.ContentMemo as VisitContentMemo,dict1.Name as StudentStatusNamefrom FM_Student stu left join FM_StudentClass sc on stu.Code=sc.Codeleft join FM_Class cls on cls.ClassCode=sc.ClassCode left join FM_Course cou on cou.Id=cls.CourseId left join FM_CourseSubject cs on cs.Id=cou.CourseSubjectIdleft join FM_StudentContact cc on cc.StudentId=stu.Idleft join FM_SchoolArea scl on scl.Id=stu.SchoolAreaIdleft join FM_StudentVisitRecord svr on svr.Id=(select top 1 svr1.Id from FM_Student stu1join FM_StudentVisitRecord svr1 on stu1.Code=svr1.Code and stu1.Id=stu.Id order by svr1.Id desc)left join Sys_User u1 on u1.Id=svr.FollowUserIDleft join Sys_Dict dict1 on dict1.Type='{1}' and dict1.Code=stu.StudentStatuswhere stu.DelFlg<>{0}

select distinct stu.*,scl.Name as SchoolAreaName,dbo.GetClassNameByStudentCode(stu.Code) as ClassName,dbo.GetCourseNameByStudentCode(stu.Code) as CourseName,dbo.GetLinkmanByStudentId(stu.Id) as Linkman,dbo.GetContactByStudentId(stu.Id) as Contact,case when svr.Linkman is not NULL then svr.Linkman else dbo.GetLinkmanByStudentId(stu.Id) end as VisitLinkman,case when svr.Contact is not NULL then svr.Contact else dbo.GetContactByStudentId(stu.Id) end as VisitContact,svr.ContentMemo as VisitContentMemo,case when u1.Sname is not NULL and u1.Sname<>'' then u1.Sname else u1.Username end as VisitFollowUserName,CONVERT(CHAR(10), svr.FollowTime, 23) as VisitLastFollowTime,dict1.Name as StudentStatusName,((select sum(pf.PayAmount) from FM_PayFlow pfjoin FM_Student stu1 on pf.CustomerCode=stu1.Code and stu1.Code=stu.Code) -(select sum(psf.PayAmount) from FM_PayStepFlow psf join FM_PayFlow pf1 on psf.PayFlowId=pf1.Idjoin FM_Student stu1 on pf1.CustomerCode=stu1.Code and stu1.Code=stu.Code))  as ArrearageAmount --欠费金额from FM_Student stu left join FM_StudentClass sc on stu.Code=sc.Codeleft join FM_Class cls on cls.ClassCode=sc.ClassCode left join FM_Course cou on cou.Id=cls.CourseId left join FM_CourseSubject cs on cs.Id=cou.CourseSubjectIdleft join FM_StudentContact cc on cc.StudentId=stu.Idleft join FM_SchoolArea scl on scl.Id=stu.SchoolAreaIdleft join FM_StudentVisitRecord svr on svr.Id=(select top 1 svr1.Id from FM_Student stu1join FM_StudentVisitRecord svr1 on stu1.Code=svr1.Code and stu1.Id=stu.Id order by svr1.Id desc)left join Sys_User u1 on u1.Id=svr.FollowUserIDleft join Sys_Dict dict1 on dict1.Type='{1}' and dict1.Code=stu.StudentStatuswhere stu.DelFlg<>{0} and (sc.Status='{2}' or sc.Status is NULL         and            (select sum(pf.PayAmount)         from FM_PayFlow pf        join FM_Student stu1 on pf.CustomerCode=stu1.Code and stu1.Code=stu.Code) >        (select sum(psf.PayAmount)         from FM_PayStepFlow psf         join FM_PayFlow pf1 on psf.PayFlowId=pf1.Id        join FM_Student stu1 on pf1.CustomerCode=stu1.Code and stu1.Code=stu.Code)