通过实例简要介绍case函数的用法
创建测试表:
DROP SEQUENCE student_sequence;
CREATE SEQUENCE student_sequenceSTART WITH INCREMENT BY ;
DROP TABLE students;
CREATE TABLE students (
id NUMBER() PRIMARY KEY
first_name VARCHAR()
last_nameVARCHAR()
majorVARCHAR()
current_creditsNUMBER()
gradevarchar());
INSERT INTO students (id first_name last_name major current_creditsgrade)
VALUES (student_sequenceNEXTVAL Scott Smith Computer Science null);
INSERT INTO students (id first_name last_name major current_creditsgrade)
VALUES (student_sequenceNEXTVAL Margaret Mason History null);
INSERT INTO students (id first_name last_name major current_creditsgrade)
VALUES (student_sequenceNEXTVAL Joanne Junebug Computer Science null);
INSERT INTO students (id first_name last_name major current_creditsgrade)
VALUES (student_sequenceNEXTVAL Manish Murgratroid Economics null);
commit;
查看相应数据
SQL> select * from students;
ID FIRST_NAMELAST_NAMEMAJOR CURRENT_CREDITS GR
ScottSmithComputer Science
Margaret MasonHistory
Joanne JunebugComputer Science
ManishMurgratroid Economics
更新语句
update students
set grade = (
select grade from
(
select id
case when current_credits > then a
when current_credits > then b
when current_credits > then c
else d end grade
from students
) a
where aid = studentsid
)
/
更新后结果
SQL> select * from students;
ID FIRST_NAME LAST_NAMEMAJOR CURRENT_CREDITS GR
Scott SmithComputer Science a
MargaretMasonHistory b
JoanneJunebugComputer Science c
ManishMurgratroidEconomics d
