34,590
社区成员
发帖
与我相关
我的任务
分享
CREATE PROCEDURE proStatisticMark
AS
BEGIN
DECLARE @less CHAR(4), --等级
@grade INT,
@sNo VARCHAR(50) --新增学号变量(成绩表中应该有这个字段的吧)
DECLARE curCourse CURSOR
FOR
--按学号sum得到总成绩(鉴于你下面写的count的理解哈)
SELECT sno,sum(grade) AS sum_grade
FROM sc --sc是成绩表
GROUP BY sno
OPEN curCourse
FETCH NEXT FROM curCourse INTO @sNo,@grades
--DECLARE curCourse CURSOR
--FOR
-- SELECT grade
-- FROM sc --sc是成绩表
--OPEN curCourse
--FETCH curCourse INTO @grades
--这里为何要count呢,就算要聚合不是也应该用sum的吗
--SELECT @grade = COUNT(grade)
--FROM sc --读取成绩表的每个学生的成绩
--上面这个查询完全多余的啊
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @grade < 60
BEGIN
SELECT @less = 'E'
PRINT @less
END
ELSE IF @grade >= 60
AND @grade < 70
BEGIN
SELECT @less = 'D'
PRINT @less
END
ELSE IF @grade >= 70
AND @grade < 80
BEGIN
SELECT @less = 'C'
PRINT @less
END
ELSE IF @grade >= 80
AND @grade < 90
BEGIN
SELECT @less = 'B'
PRINT @less
END
ELSE --@grade >= 90
BEGIN
SELECT @less = 'A'
PRINT @less
END
INSERT INTO RANK
VALUES
(
@sNo,
@less
)
FETCH NEXT FROM curCourse INTO @sNo,@grades
END
CLOSE curCourse --关闭游标
DEALLOCATE curCourse --释放游标
END
EXECUTE proStatisticMark
SELECT *
FROM RANK
看你的逻辑理解的大概改成这个意思,不知道理解的对不对,你看一下 FETCH curCourse INTO @grade
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF @grade < 60
BEGIN
SET @less60 = 'E'
PRINT @less60
END
IF @grade >= 60
AND @grade < 70
BEGIN
SET @b60a70 = 'D'
PRINT @b60a70
END
IF @grade >= 70
AND @grade < 80
BEGIN
SET @b70a80 = 'C'
PRINT @b70a80
END
IF @grade >= 80
AND @grade < 90
BEGIN
SET @b80a90 = 'B'
PRINT @b80a90
END
IF @grade >= 90
BEGIN
SET @more90 = 'C'
PRINT @more90
END
FETCH curCourse INTO @grade
END
FETCH curCourse INTO @grade
SELECT @grade=count(grade)FROM sc /*读取成绩表的每个学生的成绩
insert into Rank
select 学号,
case when grade<60 then 'E'
when grade>=60 and grade<70 then 'D'
when grade>=70 and grade<80 then 'C'
when grade>=80 and grade<90 then 'B'
else 'A'
end
from sc