sqlserver直接在sql里模糊查询

USE [toponline]
GO
/****** Object:  StoredProcedure [dbo].[procT_StudentStudyRecordGetPagedDataList]    Script Date: 2018/6/27 16:48:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:       
-- Create date: 2018.05.31 15:42
-- Description:    获取学员学习记录列表，通过学员名称，学员手机号，班级id查询
-- EXEC procT_StudentStudyRecordGetPagedDataList 1,20,'','',0,1,0
-- =============================================
ALTER PROCEDURE [dbo].[procT_StudentStudyRecordGetPagedDataList]
    (
      @StartIndex INT = 0 ,
      @EndIndex INT = 0 ,
      @UserName VARCHAR(MAX) = '' ,
      @UserPhone VARCHAR(MAX) = '' ,
      @ClassId INT = 0 ,
      @tid INT = 0 , --机构id    
      @type INT = 0 -- 查询的类型 不分页-0 分页-1
     )
AS
    BEGIN

-- 先获取学员的信息以及班级的id
        SELECT  s.Id ,
                s.UserId , -- 学员id
                person.Name AS UserName , -- 学员名称
                tu.Phone , -- 学员手机号
                CASE s.Status
                  WHEN 0 THEN s.EnrollDate
                  ELSE s.EditTime
                END AS enrollDate , -- 报名时间
                CASE s.Status
                  WHEN 0 THEN s.ClassId
                  ELSE s.TransferToClassId
                END AS ClassId , -- 班级id    
                CONVERT(NVARCHAR(50), '') AS ClassName ,-- 所属班级名称
                CONVERT(DATETIME, '') AS RecentStudyTime , -- 最近学习时间
                0 AS LiveSectionCount , -- 看直播小节数量
                0 AS LiveSectionDuration , -- 看直播分钟
                0 AS VideoSectionCount , -- 看录播小节数量
                0 AS VideoSectionDuration , -- 看录播分钟
                0 AS FaceTime , -- 面授次数
                0 AS FaceDuration   -- 面授分钟
        INTO    #TEMP
        FROM    T_StudentClass s
                INNER JOIN topmember..T_User tu ON tu.Id = s.UserId
                INNER JOIN topmember..T_Person person ON person.UserId = tu.Id
                INNER JOIN dbo.T_Class cla ON cla.Id = s.ClassId
        WHERE   1 = 1
                AND s.IsDel = 0
                AND s.IsUsed = 1
                AND s.Status <> 2
                AND ( ( s.Status = 0 )
                      OR ( s.Status = 1
                           AND s.TransferToClassId = 0
                         )
                    )
                AND cla.TrainingInstitutionId = @tid
                AND ( ( LEN(@UserName) = 0 )
                      OR ( tu.UserName LIKE '%' + @UserName + '%' )
                    )
                AND ( ( LEN(@UserPhone) = 0 )
                      OR ( tu.Phone LIKE '%' + @UserPhone + '%' )
                    )
                AND ( ( @ClassId = 0 )
                      OR ( ClassId = @ClassId )
                    );
           
-- 填充班级名称
        UPDATE  #TEMP
        SET     ClassName = c.Name
        FROM    dbo.T_Class c
        WHERE   #TEMP.ClassId = c.Id;

-- 填充看录播的情况
        UPDATE  #TEMP
        SET     RecentStudyTime = VideoRecord.VideoTime , -- 最近看录播时间
                VideoSectionCount = VideoRecord.VideoCount ,-- 看录播小节数量
                VideoSectionDuration = VideoRecord.VideoSum -- 看录播时间累计(分钟)
        FROM    ( SELECT    videoHis.UserId AS UserId ,
                            course.ClassId AS ClassId ,
                            MAX(ISNULL(videoHis.StartTime, '')) AS VideoTime , -- 最近看录播时间
                            COUNT(DISTINCT videoHis.CourseSectionId) AS VideoCount ,-- 看录播小节数量
                            SUM(ISNULL(videoHis.TimeView, 0)) AS VideoSum-- 看录播时间累计        
                  FROM      dbo.T_UserVideoHistory videoHis
                            LEFT JOIN T_StudentClass stuClass ON videoHis.UserId = stuClass.UserId
                            LEFT JOIN dbo.T_Course course ON course.Id = videoHis.CourseId
                            LEFT JOIN dbo.T_Class cla ON cla.Id = course.ClassId
                  WHERE     1 = 1
                            AND videoHis.IsDel = 0
                            AND videoHis.IsUsed = 1
                            AND stuClass.Status <> 2
                            AND cla.TrainingInstitutionId = @tid
                  GROUP BY  videoHis.UserId ,
                            course.ClassId
                ) AS VideoRecord
        WHERE   1 = 1
                AND VideoRecord.UserId = #TEMP.UserId
                AND VideoRecord.ClassId = #TEMP.ClassId;

-- 填充看直播情况
        UPDATE  #TEMP
        SET     RecentStudyTime = LiveRecord.LiveDate ,-- 最近看直播时间
                LiveSectionCount = LiveRecord.LiveCount ,-- 看直播小节数量
                LiveSectionDuration = LiveRecord.LiveSum  -- 看直播分钟累计
        FROM    ( SELECT    aLog.UserId AS UserId ,
                            course.ClassId AS ClassId ,
                            ( SELECT    MAX(live1.StartTime) AS LiveDate  -- 最近看直播时间
                                  FROM      dbo.T_VideoLiveUsers live1
                                            LEFT JOIN dbo.T_CourseSection section1 ON live1.CourseSectionId = section1.Id
                                            LEFT JOIN dbo.T_CourseChapter chapter1 ON chapter1.Id = section1.CourseChapterId
                                            LEFT JOIN dbo.T_Course course1 ON chapter1.CourseId = course1.Id
                                            LEFT JOIN dbo.T_Class cla1 ON cla1.Id = course1.ClassId
                                  WHERE     cla1.TrainingInstitutionId = @tid
                                            AND course1.ClassId = course.ClassId
                                            AND live1.UserId = aLog.UserId
                                ) AS LiveDate ,-- 最近看直播时间
                            COUNT(DISTINCT aLog.SectionId) AS LiveCount ,-- 看直播小节数量
                            SUM(CEILING(CONVERT (DECIMAL, aLog.Duration) / 60)) AS LiveSum  -- 看直播分钟累计
                  FROM      T_UserAttendanceLog aLog
                            LEFT JOIN dbo.T_Course course ON aLog.CourseId = course.Id
                            LEFT JOIN dbo.T_Class cla ON cla.Id = course.ClassId
                  WHERE     1 = 1
                            AND aLog.IsDel = 0
                            AND aLog.IsUsed = 1
                            AND aLog.[Type] = 1
                            AND cla.TrainingInstitutionId = @tid
                  GROUP BY  aLog.UserId ,
                            course.ClassId
                ) AS LiveRecord
        WHERE   1 = 1
                AND LiveRecord.UserId = #TEMP.UserId
                AND LiveRecord.ClassId = #TEMP.ClassId;
    
-- 填充面授情况
        UPDATE  #TEMP
        SET     RecentStudyTime = FaceRecourd.FaceTime ,-- 最近面授时间
                FaceTime = FaceRecourd.FaceCount ,-- 面授次数
                FaceDuration = FaceRecourd.FaceSum -- 面授分钟累计
        FROM    ( SELECT    fLog.UserId AS UserId ,
                            course.ClassId AS ClassId ,
                            MAX(fLog.CreateTime) AS FaceTime ,-- 最近面授时间
                            COUNT(DISTINCT fLog.SectionId) AS FaceCount ,-- 面授次数
                            SUM(CEILING(CONVERT (DECIMAL, fLog.Duration) / 60)) AS FaceSum -- 面授分钟累计
                  FROM      T_UserAttendanceLog fLog
                            LEFT JOIN dbo.T_Course course ON fLog.CourseId = course.Id
                            LEFT JOIN dbo.T_Class cla ON cla.Id = course.ClassId
                  WHERE     1 = 1
                            AND fLog.IsDel = 0
                            AND fLog.IsUsed = 1
                            AND fLog.[Type] = 2
                            AND cla.TrainingInstitutionId = @tid
                  GROUP BY  fLog.UserId ,
                            course.ClassId
                ) AS FaceRecourd
        WHERE   1 = 1
                AND FaceRecourd.UserId = #TEMP.UserId
                AND FaceRecourd.ClassId = #TEMP.ClassId;

    
    -- 构造完临时表之后进行查询并分页
        IF @type = 1
            BEGIN            

                SELECT  *
                FROM    ( SELECT    * ,
                                    ROW_NUMBER() OVER ( ORDER BY RecentStudyTime DESC ) AS Num ,
                                    COUNT(1) OVER ( ) AS TotalNum
                          FROM      #TEMP
                          WHERE     RecentStudyTime > CONVERT(DATETIME, '')
                        ) a
                WHERE   a.Num BETWEEN @StartIndex AND @EndIndex;
            END;
        ELSE
            BEGIN
                SELECT  *
                FROM    #TEMP
                WHERE   RecentStudyTime > CONVERT(DATETIME, '');
            END;
        DROP TABLE #TEMP;
        SET NOCOUNT ON;
    END;
    SET ANSI_NULLS ON;