1 基礎(chǔ)數(shù)據(jù)

 1 /*一 模擬數(shù)據(jù)說明：從2000年到當(dāng)年，每年添加100個學(xué)生*/ 2 Declare @StuCount int, /*每年添加的數(shù)量*/ 3         @StartYear int,/*初始年份*/ 4         @CurYear int    /*當(dāng)前年份*/ 5 Begin 6     /*設(shè)置添加數(shù)據(jù)的初始值*/ 7     SET @StuCount=100 8     SET @StartYear=2010 9     SET @CurYear=YEAR(GETDATE())10     11     /*1 向?qū)W年表添加數(shù)據(jù)*/12     Declare @XnKaishi INT /*開始年份*/13     SET @XnKaishi=@StartYear14     WHILE(@XnKaishi<=@CurYear)15         BEGIN16             IF NOT EXISTS(SELECT 1 FROM SchoolYear WHERE SyStartYear=@XnKaishi AND SyEndYear=@XnKaishi+1)17                 begin18                     insert into SchoolYear(SyStartYear,SyEndYear) values(@XnKaishi,@XnKaishi+1)19                 end20             SET @XnKaishi=@XnKaishi+121         END22     23     24     /*2 向?qū)W生表中添加數(shù)據(jù)*/25     Declare @temSc int, /*記錄當(dāng)前的學(xué)生ID*/26             @temXueHao NVARCHAR(12),/*當(dāng)前學(xué)生的編號*/27             @temXm nvarchar(20),/*學(xué)生的姓名*/28             @temSy int  /*記錄當(dāng)前的年份*/29     set @temSy=@StartYear30     /*循環(huán)年份*/31     WHILE(@temSy<=@CurYear)32         BEGIN33             /*循環(huán)添加該年份的學(xué)生*/34             set @temSc=135             while(@temSc<=@StuCount)36                 begin37                     set @temXueHao=CONVERT(varchar(4),@temSy)38                                   +'-'39                                   +CONVERT(varchar(4),@temSc)40                     if not exists(select 1 from Student where StuNO=@temXueHao)41                         begin42                             set @temXm='學(xué)生'+CONVERT(varchar(4),@temSc)43                             INSERT INTO Student(StuNO,StuName,StuJoinYear) VALUES(@temXueHao,@temXm,@temSy)44                         end45                     set @temSc=@temSc+146                 end47             SET @temSy=@temSy+148         END        49 End50 51 GO52 53 /*二 模擬科目*/54 Declare @KcCount int /*課程數(shù)量*/55 BEGIN56     SET @KcCount=2057     DECLARE @i int,58             @kcMc nvarchar(30)59     set @i=160     while(@i<=@KcCount)61         begin62             set @kcMc='課程'+CONVERT(varchar(2),@i)63             if not exists(select 1 from Course where CourseName=@kcMc)64                 begin65                     insert into Course(CourseName) values(@kcMc)66                 end67             set @i=@i+168         end69 END70 GO71 72 /*三 班級模擬：暫定三個年級，每個年級有三個班級*/73 Declare @GradeNo int=1,/*年級編號*/74         @ClassNo int=1,/*班級編號*/75         @GcName  nvarchar(10)/*年級班級名稱*/76 BEGIN77     WHILE(@GradeNo<=3)78         BEGIN79             SET @ClassNo=180             WHILE(@ClassNo<=3)81                 BEGIN82                     SET @GcName=CONVERT(varchar(1),@GradeNo)+'年級'+CONVERT(varchar(1),@ClassNo)+'班級'83                     if not exists(select 1 from GradeClass where GradeNo=@GradeNo and ClassNo=@ClassNo)84                         begin85                             INSERT INTO GradeClass(GradeNo,ClassNo,GcName) values(@GradeNo,@ClassNo,@GcName)86                         end87                     SET @ClassNo=@ClassNo+188                 END89             set @GradeNo=@GradeNo+190         END91 END92 GO

2 注冊課程數(shù)據(jù)

  1 /*2 模擬CourseRegist（課程注冊表）的數(shù)據(jù)*/  2   3 --Declare @MixRegCount int=5,/*每學(xué)年允許的最小注冊課程數(shù)*/  4 --        @MaxRegCount int=12/*每學(xué)年運(yùn)行的最大注冊課程數(shù)*/  5 BEGIN  6     /*1 臨時表：#TemPReg用于中間的數(shù)據(jù)*/  7     if   object_id('tempdb..#TempReg')   is   not   null   8           BEGIN  9             drop   table   #TempReg 10           END 11     SELECT * INTO #TempReg FROM CourseRegist where 1=2 12      13     INSERT INTO #TempReg(StuNO,SyID,CourseID) 14     SELECT Student.StuNO, 15             SchoolYear.SyID, 16             Course.CourseID 17     FROM Student 18     CROSS JOIN SchoolYear 19     CROSS JOIN Course; 20      21     /*2 刪除掉非法的數(shù)據(jù)*/ 22     DELETE  #TempReg from #TempReg T1 23         WHERE EXISTS 24         ( 25             SELECT * FROM  26             ( 27                 /*這些記錄都是不合法的記錄*/ 28                 SELECT T.CRID, /*成績表ID*/ 29                 T.StuNO,/*學(xué)號*/ 30                 Student.StuJoinYear,/*入學(xué)年份*/ 31                 SchoolYear.SyStartYear,/*學(xué)年開始年份*/ 32                 SchoolYear.SyEndYear /*學(xué)年結(jié)束年份*/ 33         FROM #TempReg T 34         LEFT JOIN Student on Student.StuNO=T.StuNO 35         LEFT JOIN SchoolYear ON SchoolYear.SyID=T.SyID 36         where Student.StuJoinYear>SchoolYear.SyStartYear /*入學(xué)年份大于學(xué)年開始年份*/ 37                 OR SchoolYear.SyStartYear>YEAR(GETDATE())/*該學(xué)年還沒有到*/ 38                 /*該學(xué)年還沒有過完。新學(xué)年從本年的9月1號到第二年的6月30號*/ 39                 OR(GETDATE() BETWEEN  40                              CONVERT(datetime,convert(varchar(4),SchoolYear.SyStartYear)+'-09-01') 41                              AND  42                              CONVERT(datetime,convert(varchar(4),SchoolYear.SyEndYear)+'-06-30') 43                    ) 44                 OR SchoolYear.SyStartYear>=Student.StuJoinYear+3/*學(xué)生入學(xué)年限已到了3年，即已畢業(yè)了*/ 45             ) T2  46             WHERE T2.CRID=T1.CRID 47         ); 48          49     /*3 隨機(jī)刪除注冊信息：之所以做這一步是因?yàn)椴⒎敲總€學(xué)生都注冊所有的課程*/ 50     DECLARE @SyID int, 51             @StuNO NVARCHAR(12), 52             @topNum int,/*隨機(jī)取出的課程數(shù)量*/ 53             @DeleteSql varchar(2000) /*用于隨機(jī)刪除的語句*/ 54     /*第一個游標(biāo)CUR_SY：用于取學(xué)年ID*/ 55     DECLARE CUR_SY CURSOR FOR SELECT SyID FROM SchoolYear 56     open CUR_SY 57     fetch next from CUR_SY into @SyID 58      59     while (@@fetch_status=0) 60         BEGIN 61             /*第二個游標(biāo)CUR_STU：用于取學(xué)生表編號*/ 62             DECLARE CUR_StuNO CURSOR FOR SELECT StuNO FROM Student 63             open CUR_StuNO 64             fetch next from CUR_StuNO into @StuNO 65              66             while(@@fetch_status=0) 67                 begin 68                     IF EXISTS(SELECT 1 FROM #TempReg WHERE StuNo=@StuNO and Syid=@SyID) 69                         BEGIN 70                             /*隨機(jī)刪除注冊課程*/ 71                             select @topNum=cast(ceiling(rand() * (SELECT COUNT(1) FROM Course)) as int) 72                             SET @DeleteSql=N'DELETE #TempReg ' 73                                        +N' WHERE StuNo='''+@StuNO 74                                        +''' and Syid='+convert(varchar(2),@SyID) 75                                        +' AND CourseID not IN (SELECT TOP '+convert(varchar(2),@topNum)+' CourseID FROM Course ORDER BY NEWID())' 76                             exec (@DeleteSql) 77                         END 78                     fetch next from CUR_StuNO into @StuNO 79                 end 80             close CUR_StuNO 81             deallocate CUR_StuNO 82              83             fetch next from CUR_SY into @SyID 84         END 85     close CUR_SY 86     deallocate CUR_SY 87      88     /*4 把處理好的臨時表信息放到物理表中*/ 89     Merge into CourseRegist CR 90         using  91         ( 92             select StuNO, 93                     SyID, 94                     CourseID 95             from #TempReg 96         ) T 97         ON CR.StuNO=T.StuNO 98             AND CR.SyID=T.SyID 99             AND CR.CourseID=T.CourseID100         WHEN NOT MATCHED THEN INSERT (StuNO,SyID,CourseID) VALUES(T.StuNO,T.SyID,T.CourseID);101 END

3 成績數(shù)據(jù)

 1 /*三 模擬成績*/ 2  3 /*模擬成績要注意： 4 1 成績都是隨機(jī)生成的，使用floor(rand()*100) 5 2 當(dāng)前假設(shè)學(xué)生經(jīng)歷3個學(xué)年需要畢業(yè)，所以只能在3個學(xué)年有成績。 6   如入學(xué)年份2012年，則只能在2012-2013學(xué)年，2013-2014學(xué)年，2014-2015學(xué)年有成績 7   但當(dāng)前2014-2015學(xué)年還沒有過完，所以2014-2015學(xué)年亦沒有成績 8 3 該表中的學(xué)號、課程ID、學(xué)年ID來自于CourseRegist表 9 */10 Merge into Score USING11 (12     SELECT StuNO,13         SyID,14         CourseID,15         CASE 16         /*超過一百分則使用100分減去40范圍之內(nèi)的隨機(jī)數(shù)，保證分?jǐn)?shù)在60至100之間*/17         WHEN ScoreValue>=100 THEN 100-cast(ceiling(rand(checksum(newid()))*40) as int)    18         ELSE ScoreValue19         END AS ScoreValue20     FROM21     (22         /*組合最初的數(shù)據(jù)*/    23         SELECT StuNO,SyID,CourseID,24                 /*之所以隨機(jī)數(shù)乘以500，是為了避免有過多的不及格的分?jǐn)?shù)*/25                cast(ceiling(rand(checksum(newid()))*500) as int) AS ScoreValue,26                /*KeepFlag：保留標(biāo)記位。*/27                cast(ceiling(rand(checksum(newid()))*10) as int) AS KeepFlag    28         FROM CourseRegist29     ) A where KeepFlag>1    /*保留十分之九的數(shù)據(jù)*/30 ) B31 ON Score.StuNO=B.StuNO 32     AND Score.SyID=B.SyID 33     AND Score.CourseID=B.CourseID 34 WHEN NOT MATCHED T