关于交叉报表的动态实现

很多时候我们需要得到交叉列表，最近做课程设计就需要这样的功能，比如要得到一个班某学期的成绩，因为每一学期所学科目和数量都是变化的，这就要求动态的查询以得到成绩。而且得到这样的显示形式：
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

至此，该存取过程实现了我们想要的功能.第一次发文章，请大家多多支持！

本文地址：http://com.8s8s.com/it/it8334.htm