Update和Select结合统计更新

update table_a set updatetime=getdate(), name=b.name

from (select name,age from table_b where table_b.Id=1) as b

where table_a.id=1

UPDATE A SET A1 = B1, A2 = B2, A3 = B3 FROM A, B WHERE A.ID = B.ID

update Table_Main set DataStatusId='902e0650-4fd2-432b-ad56-0750ba81f71e',

        MonthClassHourSum=tmain.MonthClassHourSum,FTCNClassHourSum=tmain.FTCNClassHourSum,PTCNClassHourSum=tmain.PTCNClassHourSum,

        FTENClassHourSum=tmain.FTENClassHourSum,PTENClassHourSum=tmain.PTENClassHourSum,

        StaffClassHourSum=tmain.StaffClassHourSum, TakeClassHourSum=tmain.TakeClassHourSum,

        TeachingTotalSum=tmain.TeachingTotalSum, TeachingClassSum=tmain.TeachingClassSum,

        TeachingStudentSum=tmain.TeachingStudentSum, StudentLearnAge=tmain.StudentLearnAge,

        TeachingPercent=tmain.TeachingPercent,TotalAttendStudentCnt=tmain.TeachingStudentSum, TotalClassTeacherCount=tmain.TotalClassTeacherCount,

        TotalTeacherCount=tmain.TotalTeacherCount, GroupTeachItemCourse=tmain.GroupTeachItemCourse

        from

       (select sum(TeacherClassHour)as 'MonthClassHourSum',

        sum(Case when TeacherTypeId='t1' then TeacherClassHour else 0 end) as 'FTCNClassHourSum',

        sum(Case when TeacherTypeId='t2' then TeacherClassHour else 0 end) as 'PTCNClassHourSum',

        sum(Case when TeacherTypeId='t3' then TeacherClassHour else 0 end) as 'FTENClassHourSum',

        sum(Case when TeacherTypeId='t4' then TeacherClassHour else 0 end) as 'PTENClassHourSum',

        sum(Case when TeacherTypeId='t5' then TeacherClassHour else 0 end) as 'StaffClassHourSum',

        sum(Case when TeacherTypeId='t6' then TeacherClassHour else 0 end) as 'TakeClassHourSum',

        COUNT(DISTINCT Id) as 'TeachingTotalSum',COUNT(DISTINCT TeachItemClassId) as 'TeachingClassSum',sum(AttendStudentCnt) as 'TeachingStudentSum',

        Convert(decimal(18,2),sum(TeacherClassHour)/COUNT(DISTINCT TeacherUserID)) as 'StudentLearnAge',

        100*Convert(decimal(18,2),COUNT(DISTINCT TeacherUserID)/(select count(DISTINCT IdCard) from Table_User)) as 'TeachingPercent',

        COUNT(DISTINCT TeacherUserID) as 'TotalClassTeacherCount',(select count(DISTINCT IdCard) from Table_User) as 'TotalTeacherCount',

        (select count(DISTINCT Id) from [dbo].[Table_Group] where FKMainId=@mainId) as 'GroupTeachItemCourse'

        from [dbo].[Table_Detail] where FKTMainId=@mainId

        ) tmain

        where Tabel_Main.Id=@mainId

SQL

巴特西

Update和Select结合统计更新

最新文章

热门文章