優化Mychool 第六章上機


BEGIN TRANSACTION  
DECLARE @errorSum INT  
SET @errorSum=0  
/*--插入數據--*/  
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)  
            VALUES(1,1,GETDATE(),90)  
SET @[email protected]+@@error  
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)  
            VALUES(1,1,GETDATE(),70)  
SET @[email protected]+@@error  
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)  
            VALUES(2,1,GETDATE(),67)  
SET @[email protected]+@@error  
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)  
            VALUES(2,1,GETDATE(),55)  
SET @[email protected]+@@error  
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)  
            VALUES(3,1,GETDATE(),102)--分數違反約束  
SET @[email protected]+@@error  
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)  
            VALUES(3,4,GETDATE(),90)  
SET @[email protected]+@@error  
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)  
            VALUES(4,4,GETDATE(),56)  
SET @[email protected]+@@error  
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)  
            VALUES(4,4,GETDATE(),88)  
SET @[email protected]+@@error  
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)  
            VALUES(5,4,GETDATE(),40)  
SET @[email protected]+@@error  
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)  
            VALUES(5,4,GETDATE(),65)  
SET @[email protected]+@@error  
  
/*--根據是否有錯誤,確定事務是提交還是撤銷--*/  
IF(@errorSum<>0) --如果有錯誤  
  BEGIN  
    PRINT '插入失敗,回滾事務'  
    ROLLBACK TRANSACTION   
  END    
ELSE  
  BEGIN  
    PRINT '插入成功,提交事務'  
    COMMIT TRANSACTION     
  END  
GO  


BEGIN TRANSACTION  
DECLARE @errorSum INT  
SET @errorSum=0  
/*--查詢Result表中所有Y2學員的考試成績,保存到新表HistoreResult*/  
SELECT Result.* INTO HistoreResult   
FROM Result INNER JOIN Student ON Result.StudentNo=Student.StudentNo  
INNER JOIN Grade ON Grade.GradeId=Student.GradeId   
WHERE GradeName='Y2'  
SET @[email protected]+@@error  
  
/*--刪除Result表中所有Y2學員的考試成績*/  
DELETE  Result FROM  Result JOIN Student ON Result.StudentNo=Student.StudentNo  
INNER JOIN Grade ON Grade.GradeId=Student.GradeId   
WHERE GradeName='Y2'  
SET @[email protected]+@@error  
  
/*--將Student表中所有Y2的學員記錄,保存到新表HistoryStudent*/  
SELECT Student.* INTO HistoryStudent  
FROM Student INNER JOIN Grade ON Grade.GradeId=Student.GradeId   
WHERE GradeName='Y2'  
SET @[email protected]+@@error  
  
/*--刪除Studet表中所有Y2學員記錄*/  
DELETE Student FROM Student   
INNER JOIN Grade ON Grade.GradeId=Student.GradeId   
WHERE GradeName='Y2'  
SET @[email protected]+@@error  
  
/*--根據是否有錯誤,確定事務是提交還是撤銷--*/  
IF (@errorSum<>0) --如果有錯誤  
  BEGIN  
    PRINT '插入失敗,回滾事務'  
    ROLLBACK TRANSACTION   
  END    
ELSE  
  BEGIN  
    PRINT '插入成功,提交事務'  
    COMMIT TRANSACTION     
  END  
GO  


CREATE VIEW vw_student_result_info  
AS  
  SELECT 姓名=StudentName,學號=Student.StudentNo,  
                 聯系電話=Phone,學期=GradeName,成績=Total  
  FROM Student  
  LEFT OUTER JOIN (  
      SELECT r.StudentNo,GradeName,SUM(StudentResult) Total   
      FROM Result r   
      INNER JOIN (  
         SELECT StudentNo,SubjectNo,MAX(ExamDate)  ExamDate   
         FROM Result   
         GROUP BY StudentNo,SubjectNo) tmp   
      ON r.ExamDate=tmp.ExamDate  
      AND r.SubjectNo = tmp.SubjectNo AND r.StudentNo = tmp.StudentNo  
      INNER JOIN Subject sub ON sub.SubjectNo = r.SubjectNo  
      INNER JOIN Grade g ON g.GradeId = sub.GradeId  
      GROUP By r.StudentNo,GradeName ) TmpResult2   
 ON Student.StudentNo = TmpResult2.StudentNo  
 GROUP BY StudentName,Student.StudentNo,Phone,GradeName,Total  
GO  
SELECT * FROM vw_student_result_info  


USE MySchool  
GO  
  
IF EXISTS (SELECT name FROM sysindexes  
          WHERE name = 'IX_Result_StudentResult')  
   DROP INDEX Result.IX_Result_StudentResult  --刪除索引  
GO  
  
--考試成績列上創建可重復索引:填充因子為70%--  
CREATE INDEX IX_Result_StudentResult  
   ON Result(StudentResult)  
       WITH FILLFACTOR = 70  
GO  
  
  
--查詢輸出學生姓名、課程名稱、考試日期和考試成績   
SELECT StudentName '學生姓名',SubjectName '課程名稱',ExamDate '考試日期', StudentResult '考試成績' FROM Subject,Student,Result  
WITH (INDEX = IX_Result_StudentResult)  
WHERE StudentResult BETWEEN 80 AND 90 AND Student.StudentNo = Result.StudentNo AND Subject.SubjectId = Result.SubjectId  

0 個評論

要回覆文章請先登錄註冊