另外一个想要插入的新字段是成绩评价,也就是不及格、及格、良好、优秀,应该怎么实现,我使用了游标,但是还不能为每条记录插入准确的成绩评价,我是个SQL SERVER初学者,请各位赐教,谢谢!×××××××××××××××××××××××××××××××××××××
--新建一个存储过程
--按输入的学号查询学生成绩
--根据平均成绩对学生成绩作出评价
if exists (select name = '显示成绩情况' from sysobjects where name = '显示成绩情况' and type = 'P')
drop procedure 显示成绩情况
gocreate procedure 显示成绩情况
@stuID char(10) = null
as--定义一个循环游标
declare temp_cur scroll cursor
for
select * from 单科成绩表
open temp_cur--定义循环变量
declare @average int
declare @grade char(10)
declare @number int
set @number = @@cursor_rows
declare @num int
set @num = 0--执行存储过程时没有输入学号
if(@stuID is null)
begin
while(@num <= @number)
begin
set @num = @num + 1
fetch absolute @num from temp_cur
select @average = ([期中成绩] + [期末成绩]) / 2 from 单科成绩表
set @grade = case
when @average < 60 then '不及格'
when @average >= 60 and @average < 75 then '及格'
when @average >= 75 and @average < 90 then '良好'
when @average >= 90 and @average < 100 then '优秀'
else 'error'
end
select [学号], [姓名], [期中成绩], [期末成绩], ([期中成绩] + [期末成绩])/2 as '平均成绩', @grade as '评价', [教师姓名]
from 单科成绩表end
end
--执行存储过程时输入一个学号
else
begin
select @average = ([期中成绩] + [期末成绩]) / 2 from 单科成绩表
where 单科成绩表.[学号] = @stuID
set @grade = case
when @average < 60 then '不及格'
when @average >= 60 and @average < 75 then '及格'
when @average >= 75 and @average < 90 then '良好'
when @average >= 90 and @average < 100 then '优秀'
else 'error'
end
select [学号], [姓名], [期中成绩], [期末成绩], ([期中成绩] + [期末成绩])/2 as '平均成绩', @grade as '评价', [教师姓名]
from 单科成绩表
where 单科成绩表.[学号] = @stuID
end
go
×××××××××××××××××××××××××××××××××××××××××××××××
--新建一个存储过程
--按输入的学号查询学生成绩
--根据平均成绩对学生成绩作出评价
if exists (select name = '显示成绩情况' from sysobjects where name = '显示成绩情况' and type = 'P')
drop procedure 显示成绩情况
gocreate procedure 显示成绩情况
@stuID char(10) = null
as--定义一个循环游标
declare temp_cur scroll cursor
for
select * from 单科成绩表
open temp_cur--定义循环变量
declare @average int
declare @grade char(10)
declare @number int
set @number = @@cursor_rows
declare @num int
set @num = 0--执行存储过程时没有输入学号
if(@stuID is null)
begin
while(@num <= @number)
begin
set @num = @num + 1
fetch absolute @num from temp_cur
select @average = ([期中成绩] + [期末成绩]) / 2 from 单科成绩表
set @grade = case
when @average < 60 then '不及格'
when @average >= 60 and @average < 75 then '及格'
when @average >= 75 and @average < 90 then '良好'
when @average >= 90 and @average < 100 then '优秀'
else 'error'
end
select [学号], [姓名], [期中成绩], [期末成绩], ([期中成绩] + [期末成绩])/2 as '平均成绩', @grade as '评价', [教师姓名]
from 单科成绩表end
end
--执行存储过程时输入一个学号
else
begin
select @average = ([期中成绩] + [期末成绩]) / 2 from 单科成绩表
where 单科成绩表.[学号] = @stuID
set @grade = case
when @average < 60 then '不及格'
when @average >= 60 and @average < 75 then '及格'
when @average >= 75 and @average < 90 then '良好'
when @average >= 90 and @average < 100 then '优秀'
else 'error'
end
select [学号], [姓名], [期中成绩], [期末成绩], ([期中成绩] + [期末成绩])/2 as '平均成绩', @grade as '评价', [教师姓名]
from 单科成绩表
where 单科成绩表.[学号] = @stuID
end
go
×××××××××××××××××××××××××××××××××××××××××××××××
解决方案 »
- 问一个菜鸟级问题
- 这个触发器比较难写的:A表的某些记录,只能由B表的触发器来操纵(插删改),但不允许用户直接操纵。
- 一道题
- 谢谢各位高手,帮我看看为什么不显示数据!
- 非递归中序遍历二叉树算法具体是什么?
- Excel导入至sqlserver问题
- 新手入门,简单问题,高分相求,真的很急,来者有分,在线等.谢谢了
- 菜鸟问题:如何连接到远程SQL Server服务器?(up也有分)
- 问:什么叫逻辑?什么叫逻辑数据结构??明早给分。急!!
- SQL2012安装(数据库引擎服务、SQL复制失败)64位。之前win8.1现win10,同样问题
- 一个论坛代码中的数据库查询语句的问题
- 为什么网站老需要重新登陆两次才可以正常使用
Try:--新建一个存储过程
--按输入的学号查询学生成绩
--根据平均成绩对学生成绩作出评价
if exists (select name = '显示成绩情况' from sysobjects where name = '显示成绩情况' and type = 'P')
drop procedure 显示成绩情况
go create procedure 显示成绩情况
@stuID char(10) = null
as
--执行存储过程时没有输入学号
if(@stuID is null)
begin
select [学号], [姓名], [期中成绩], [期末成绩], ([期中成绩] + [期末成绩])/2 as '平均成绩',
case when ([期中成绩] + [期末成绩])/2<60 then '不及格'
when ([期中成绩] + [期末成绩])/2 >= 60 and ([期中成绩] + [期末成绩])/2 < 75 then '及格'
when ([期中成绩] + [期末成绩])/2 >= 75 and ([期中成绩] + [期末成绩])/2 < 90 then '良好'
when ([期中成绩] + [期末成绩])/2 >= 90 and ([期中成绩] + [期末成绩])/2 <= 100 then '优秀'
else 'error'
end as '评价',
[教师姓名]
from 单科成绩表
end
else
select [学号], [姓名], [期中成绩], [期末成绩], ([期中成绩] + [期末成绩])/2 as '平均成绩',
case when ([期中成绩] + [期末成绩])/2<60 then '不及格'
when ([期中成绩] + [期末成绩])/2 >= 60 and ([期中成绩] + [期末成绩])/2 < 75 then '及格'
when ([期中成绩] + [期末成绩])/2 >= 75 and ([期中成绩] + [期末成绩])/2 < 90 then '良好'
when ([期中成绩] + [期末成绩])/2 >= 90 and ([期中成绩] + [期末成绩])/2 <= 100 then '优秀'
else 'error'
end as '评价',
[教师姓名]
from 单科成绩表
where 单科成绩表.[学号] = @stuID go
××××××××××××××××××××××××××××
简化下:--新建一个存储过程
--按输入的学号查询学生成绩
--根据平均成绩对学生成绩作出评价
if exists (select name = '显示成绩情况' from sysobjects where name = '显示成绩情况' and type = 'P')
drop procedure 显示成绩情况
go create procedure 显示成绩情况
@stuID char(10) = null
as
select [学号], [姓名], [期中成绩], [期末成绩], ([期中成绩] + [期末成绩])/2 as '平均成绩',
case when ([期中成绩] + [期末成绩])/2<60 then '不及格'
when ([期中成绩] + [期末成绩])/2 >= 60 and ([期中成绩] + [期末成绩])/2 < 75 then '及格'
when ([期中成绩] + [期末成绩])/2 >= 75 and ([期中成绩] + [期末成绩])/2 < 90 then '良好'
when ([期中成绩] + [期末成绩])/2 >= 90 and ([期中成绩] + [期末成绩])/2 <= 100 then '优秀'
else 'error'
end as '评价',
[教师姓名]
from 单科成绩表
where 单科成绩表.[学号] = @stuID or @stuID is nullgo
××××××××××××××××××××××××××××