游标实现成绩列显示

最新推荐文章于 2025-12-26 18:13:00 发布

原创最新推荐文章于 2025-12-26 18:13:00 发布 · 273 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#insert #table #database #null #数据库 #join

本文通过创建学生、课程及成绩表并插入数据，演示了如何使用T-SQL游标遍历课程表，构造动态SQL语句来汇总学生的各科成绩。

游标实现成绩列显示

USE master GO /*$$$$$$$$$$$$$建库$$$$$$$$$$$$$$$$$$$$$$$$*/ --检验数据库是否存在，如果为真，删除此数据库-- set nocount on IF exists(SELECT * FROM sysdatabases WHERE name='Student') DROP DATABASE Student GO CREATE DATABASE Student GO --建数据表-- USE Student GO CREATE TABLE Student --学生表 ( SID char(10) primary key, --学生号 MName CHAR(50) NOT NULL --姓名 ) GO CREATE TABLE Course --课程表 ( CID char(10) primary key, --课程号 CName CHAR(50) NOT NULL --课程名 ) GO CREATE TABLE score --学生成绩表 ( Student int identity(1,1) primary key, --成绩记录号 CID char(10) foreign key(CID) references Course(CID) , --课程号 SID char(10) foreign key(SID) references Student(SID) , --学生号 Score int NOT NULL --成绩 ) GO --课程表中插入数据-- INSERT INTO Course(CID,CName)VALUES('F001','语文') INSERT INTO Course(CID,CName)VALUES('F002','数学') INSERT INTO Course(CID,CName)VALUES('F003','英语') INSERT INTO Course(CID,CName)VALUES('F004','历史') INSERT INTO Course(CID,CName)VALUES('F005','computer') --学生表中插入数据-- INSERT INTO Student(SID,MName)VALUES('M001','张萨') INSERT INTO Student(SID,MName)VALUES('M002','王强') INSERT INTO Student(SID,MName)VALUES('M003','李三') INSERT INTO Student(SID,MName)VALUES('M004','李四') INSERT INTO Student(SID,MName)VALUES('M005','阳阳') INSERT INTO Student(SID,MName)VALUES('M006','虎子') INSERT INTO Student(SID,MName)VALUES('M007','夏雪') INSERT INTO Student(SID,MName)VALUES('M008','璐璐') INSERT INTO Student(SID,MName)VALUES('M009','珊珊') INSERT INTO Student(SID,MName)VALUES('M010','香奈儿') --成绩表中插入数据-- INSERT INTO Score(CID,SID,Score)VALUES('F001','M001',78) INSERT INTO Score(CID,SID,Score)VALUES('F002','M001',67) INSERT INTO Score(CID,SID,Score)VALUES('F003','M001',89) INSERT INTO Score(CID,SID,Score)VALUES('F004','M001',76) INSERT INTO Score(CID,SID,Score)VALUES('F001','M002',89) INSERT INTO Score(CID,SID,Score)VALUES('F002','M002',67) INSERT INTO Score(CID,SID,Score)VALUES('F003','M002',84) INSERT INTO Score(CID,SID,Score)VALUES('F004','M002',96) INSERT INTO Score(CID,SID,Score)VALUES('F001','M003',70) INSERT INTO Score(CID,SID,Score)VALUES('F002','M003',87) INSERT INTO Score(CID,SID,Score)VALUES('F003','M003',92) INSERT INTO Score(CID,SID,Score)VALUES('F004','M003',56) INSERT INTO Score(CID,SID,Score)VALUES('F001','M004',80) INSERT INTO Score(CID,SID,Score)VALUES('F002','M004',78) INSERT INTO Score(CID,SID,Score)VALUES('F003','M004',97) INSERT INTO Score(CID,SID,Score)VALUES('F004','M004',66) INSERT INTO Score(CID,SID,Score)VALUES('F001','M006',88) INSERT INTO Score(CID,SID,Score)VALUES('F002','M006',55) INSERT INTO Score(CID,SID,Score)VALUES('F003','M006',86) INSERT INTO Score(CID,SID,Score)VALUES('F004','M006',79) INSERT INTO Score(CID,SID,Score)VALUES('F002','M007',77) INSERT INTO Score(CID,SID,Score)VALUES('F003','M008',65) INSERT INTO Score(CID,SID,Score)VALUES('F004','M007',48) INSERT INTO Score(CID,SID,Score)VALUES('F004','M009',75) INSERT INTO Score(CID,SID,Score)VALUES('F002','M009',88) GO USE Student --定义变量用于接收游标行数据 if object_id('tempdb.dbo.#tmp') is not null drop table [dbo].[#tmp] GO select * into [#tmp] from Course --select * from #tmp go /* 1定义游标 2打开游标 3提示数据 4关闭游标 5销毁游标 */ declare @strField varchar(1000) set @strField='' --存储case end 语句 declare @Fid varchar(4),@FName varchar(20) declare csr cursor for select * from #tmp open csr fetch from csr into @fid,@FName --提取一行到游标 while(1=1) begin if(@@fetch_status<>0) break set @strField=@strField+rtrim(@FName)+'=sum(case when CName='''+rtrim(@FName)+''' then score end),' fetch from csr into @fid,@FName --提取下一行到游标 end close csr deallocate csr set @strField=@strField+'1' print len(@strField) declare @strSql varchar(4000) set @strSql='select MName,{0} from student a left join Score b on(a.SID=b.SID) left join Course c on (b.CID=c.CID) group by MName ' select @strSql=replace(@strSql,'{0}',rtrim(@strField)) --print @strSql execute (@strSql)