这是我建的表和数据
CREATE TABLE Candidate1 (cName CHAR(6) PRIMARY KEY,
cPhone CHAR(11),siScore SMALLINT
)
-------------------------------
INSERT INTO Candidate1 VALUES ('刘晓亮','13703236886',90)
INSERT INTO Candidate1 VALUES('孙红娟','13803992299',70)
INSERT INTO Candidate1 VALUES('李明传','13902199889',80)
------------------------------
这是我写的游标
DECLARE @Count int
DECLARE @Name CHAR(6),@Phone CHAR(11),@Score SMALLINT
DECLARE my_cur CURSOR FOR SELECT * FROM Candidate1
OPEN my_cur
select @Count=COUNT(*) FROM Candidate1
WHILE (@@FETCH_STATUS=0 AND @Count>0)
BEGIN
FETCH NEXT FROM my_cur INTO @Name,@Phone,@Score
CASE
WHEN @Score>90 THEN PRINT @Name+@Phone+'优秀'
WHEN @Score>=80 AND @Score<90 THEN PRINT @Name+@Phone+'良好'
WHEN @Score>=60 AND @Score<80 THEN PRINT @Name+@Phone+'及格'
ELSE PRINT @Name+@Phone+'不及格'
SET @Count=@Count-1
END
CLOSE my_cur
DEALLOCATE my_cur
---------------------------
我要是用if...else 结构就正确了,我想用case..when...then..结构实现
CREATE TABLE Candidate1 (cName CHAR(6) PRIMARY KEY,
cPhone CHAR(11),siScore SMALLINT
)
-------------------------------
INSERT INTO Candidate1 VALUES ('刘晓亮','13703236886',90)
INSERT INTO Candidate1 VALUES('孙红娟','13803992299',70)
INSERT INTO Candidate1 VALUES('李明传','13902199889',80)
------------------------------
这是我写的游标
DECLARE @Count int
DECLARE @Name CHAR(6),@Phone CHAR(11),@Score SMALLINT
DECLARE my_cur CURSOR FOR SELECT * FROM Candidate1
OPEN my_cur
select @Count=COUNT(*) FROM Candidate1
WHILE (@@FETCH_STATUS=0 AND @Count>0)
BEGIN
FETCH NEXT FROM my_cur INTO @Name,@Phone,@Score
CASE
WHEN @Score>90 THEN PRINT @Name+@Phone+'优秀'
WHEN @Score>=80 AND @Score<90 THEN PRINT @Name+@Phone+'良好'
WHEN @Score>=60 AND @Score<80 THEN PRINT @Name+@Phone+'及格'
ELSE PRINT @Name+@Phone+'不及格'
SET @Count=@Count-1
END
CLOSE my_cur
DEALLOCATE my_cur
---------------------------
我要是用if...else 结构就正确了,我想用case..when...then..结构实现
DECLARE @Name CHAR(6),@Phone CHAR(11),@Score SMALLINT
DECLARE my_cur CURSOR FOR SELECT * FROM Candidate1
OPEN my_cur
FETCH FROM my_cur INTO @Name,@Phone,@Score
WHILE (@@FETCH_STATUS=0 )
BEGIN
CASE --换成if else
WHEN @Score>90 THEN PRINT @Name+@Phone+'优秀'
WHEN @Score>=80 AND @Score <90 THEN PRINT @Name+@Phone+'良好'
WHEN @Score>=60 AND @Score <80 THEN PRINT @Name+@Phone+'及格'
ELSE PRINT @Name+@Phone+'不及格'
FETCH NEXT FROM my_cur INTO @Name,@Phone,@Score
END
CLOSE my_cur
DEALLOCATE my_cur
declare @msg varchar(100);set @msg=
CASE
WHEN @Score>90 THEN @Name+@Phone+'优秀'
WHEN @Score>=80 AND @Score <90 THEN @Name+@Phone+'良好'
WHEN @Score>=60 AND @Score <80 THEN @Name+@Phone+'及格'
ELSE @Name+@Phone+'不及格' end ;print @msg