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

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 StudentStatusName

from FM_Student stu 

left join FM_StudentClass sc on stu.Code=sc.Code
left 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.CourseSubjectId
left join FM_StudentContact cc on cc.StudentId=stu.Id
left join FM_SchoolArea scl on scl.Id=stu.SchoolAreaId

left join FM_StudentVisitRecord svr on svr.Id=
(select top 1 svr1.Id from FM_Student stu1
join 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.FollowUserID
left join Sys_Dict dict1 on dict1.Type='{1}' and dict1.Code=stu.StudentStatus

where 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 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))  as ArrearageAmount --欠费金额

from FM_Student stu 

left join FM_StudentClass sc on stu.Code=sc.Code
left 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.CourseSubjectId
left join FM_StudentContact cc on cc.StudentId=stu.Id
left join FM_SchoolArea scl on scl.Id=stu.SchoolAreaId

left join FM_StudentVisitRecord svr on svr.Id=
(select top 1 svr1.Id from FM_Student stu1
join 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.FollowUserID
left join Sys_Dict dict1 on dict1.Type='{1}' and dict1.Code=stu.StudentStatus

where 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)