网上评价系统需求分析
CREATE PROCEDURE updateGroupOrder AS IF EXISTS(SELECT 1 UPDATE stu_group_by_course SET iAll = --全校总排名 (SELECT COUNT(1) + 1 FROM stu_group_by_course WHERE score > s.score AND as_term = @term), iCourse = --按课程排名 (SELECT COUNT(1) + 1 FROM stu_group_by_course WHERE score > s.score AND as_term = @term AND Course = s.Course), iCourseType = --按课程类型排名 (SELECT COUNT(1) + 1 FROM stu_group_by_course WHERE score > s.score AND as_term = @term AND CourseType = s.CourseType), iDepartment = --按学院(部门)排名 (SELECT COUNT(1) + 1 FROM stu_group_by_course WHERE score > s.score AND as_term = @term AND te_department = s.te_department) FROM stu_group_by_course AS s WHERE score > 0 END
FROM dbo.sysobjects WHERE name = 'stu_group_by_course' AND type = 'U') @term nvarchar(6) BEGIN createStuCourseNum 统计学生评教课程数 第27页
网上评教系统需求分析
CREATE PROCEDURE createStuCourseNum AS BEGIN TRANSACTION SELECT s.StudentID, s.ClassID, a.CourseNum, a.Term INTO #ClassCourseNum FROM ( --分级教学 SELECT StudentID, ClassID @term nvarchar(6) FROM FJStudent UNION --选修课程 SELECT StudentID, ClassID FROM XXStudent UNION --教学班 SELECT StudentID, SUBSTRING(ClassCode, 1, 12) AS ClassID FROM StudentInfo) s INNER JOIN ( --班级评分课程数量 SELECT as_cl_id, COUNT(1) AS CourseNum,as_term AS Term FROM stu_assess_join WHERE as_term = @term GROUP BY as_cl_id,as_term) a ON s.ClassID = a.as_cl_id DROP TABLE stu_course_num --学生评分课程数量 SELECT s.StudentID, SUBSTRING(s.ClassCode,1,12) AS ClassID, c.CourseNum INTO stu_course_num FROM dbo.StudentInfo s INNER JOIN (SELECT StudentID, SUM(CourseNum) AS CourseNum FROM #ClassCourseNum GROUP BY StudentID) c ON s.StudentID = c.StudentID DROP TABLE #ClassCourseNum COMMIT SPLIT 分隔函数 第28页
网上评价系统需求分析
CREATE FUNCTION SPLIT( ) RETURNS @temp table(a varchar(100)) BEGIN DECLARE @i int SET @SourceSql=RTRIM(LTRIM(@SourceSql)) SET @i=CHARINDEX(@StrSeprate, @SourceSql) WHILE @i>=1 BEGIN INSERT @temp VALUES(left(@SourceSql, @i - 1)) SET @SourceSql = SUBSTRING(@SourceSql, @i + 1, LEN(@SourceSql) - @i) SET @i = CHARINDEX(@StrSeprate, @SourceSql) END IF @SourceSql <> '\\' INSERT @temp VALUES(@SourceSql) RETURN END @SourceSql varchar(8000), @StrSeprate varchar(10) STU_AVG 计算平均分(剔除上下7%) CREATE FUNCTION STU_AVG(@id int) RETURNS int AS BEGIN DECLARE @avg int SELECT @avg = AVG(score) FROM (SELECT TOP 92.47 PERCENT score FROM stu_score WHERE (score IN (SELECT TOP 93 PERCENT score FROM stu_score WHERE as_id = @id ORDER BY score)) AND as_id = @id ORDER BY score DESC) T RETURN @avg END
第29页
