很多时候我们需要得到交叉列表,最近做课程设计就需要这样的功能,比如要得到一个班某学期的成绩,因为每一学期所学科目和数量都是变化的,这就要求动态的查询以得到成绩。而且得到这样的显示形式: studentID studentName courseName1 courseName2 courseName3 …… 1 lupenda 66 77 88 …… 。 。 。 下面的例子就是为了实现上面的功能,该存取过程涉及3张表:
Course表: CourseID,CourseName,Duration,[Year],MajorID,Semester
StudentData表: StudentID,StudentName,ClassID……
Student_Course表: StudentID,CourseID,Score
--根据不同参数取得成绩 --可以得到某学期,某专业,某班级,某学生的成绩
CREATE procedure GetScore @Year int ,@Semester bit ,@MajorID int = null,@ClassID int = null,@StudentID int = null as
declare @var varchar(200) declare @sql varchar(8000)
set @sql=''
--放进临时表 select distinct CourseName into #tb from Course where Course.Year = @Year and Course.Semester = @Semester
--声明游标 declare cur cursor for select CourseName from #tb open cur fetch next from cur into @var while @@fetch_status=0 begin set @sql = @sql + 'sum(case when Course.CourseName = ''' +@var+ ''' then Student_Course.Score else 0 end) as '+ @var+',' fetch next from cur into @var end
set @sql = left(@sql,len(@sql)-1)
set @sql = 'select Class.ClassName as 班级,Student_Course.StudentID as 全学号,StudentData.StudentName as 姓名, ' +@sql+ ' from StudentData,Student_Course,Course ,Class where Student_Course.CourseID = Course.CourseID and StudentData.StudentID = Student_Course.StudentID and StudentData.ClassID = Class.ClassID'
--根据输入参数的不同创建查询语句 if @Year is not null set @sql = @sql+ ' and Course.Year = '+ Cast( @Year as varchar(4)) --强制转换为字符串
if @Semester is not null set @sql = @sql+ ' and Course.Semester = '+ Cast( @Semester as varchar(1))
if @MajorID is not null set @sql = @sql+ ' and Course.MajorID = '+ Cast( @MajorID as varchar(4))
if @ClassID is not null set @sql = @sql+ ' and StudentData.ClassID = '+ Cast( @ClassID as varchar(4))
if @StudentID is not null set @sql = @sql+ ' and StudentData.StudentID = '+ Cast( @StudentID as varchar(8))
set @sql = @sql+ 'group by Class.ClassName,Student_Course.StudentID,StudentData.StudentName order by Student_Course.StudentID '
exec(@sql)
deallocate cur drop table #tb GO
至此,该存取过程实现了我们想要的功能.第一次发文章,请大家多多支持! 
|