数据库表为
id first_name last_name major current_credits grade1 10000 Scott Smith Computer Science 98
2 10001 Margaret Mason History 88
3 10002 Joanne Junebug Computer Science 75
4 10003 Manish Murgratroid Economics 66 对学生成绩分级别
update students
set grade = (
select grade from
(
select id,
case when current_credits > 90 then 'a'
when current_credits > 80 then 'b'
when current_credits > 70 then 'c'
else 'd' end grade
from students
) a
where a.id = students.id
);
请问大家为什么update student 的where语句写在括号里面就可以呢,说的稍微细一点。呵呵,刚刚研究sql比较菜。
id first_name last_name major current_credits grade1 10000 Scott Smith Computer Science 98
2 10001 Margaret Mason History 88
3 10002 Joanne Junebug Computer Science 75
4 10003 Manish Murgratroid Economics 66 对学生成绩分级别
update students
set grade = (
select grade from
(
select id,
case when current_credits > 90 then 'a'
when current_credits > 80 then 'b'
when current_credits > 70 then 'c'
else 'd' end grade
from students
) a
where a.id = students.id
);
请问大家为什么update student 的where语句写在括号里面就可以呢,说的稍微细一点。呵呵,刚刚研究sql比较菜。
SET grade=(
CASE WHEN current_credits > 90 THEN 'a'
WHEN current_credits > 80 THEN 'b'
WHEN current_credits > 70 THEN 'c'
ELSE 'd'
END)
(
select id,
case when current_credits > 90 then 'a'
when current_credits > 80 then 'b'
when current_credits > 70 then 'c'
else 'd' end grade
from students
) a
where a.id = students.id 只有加上where才能保证是一个唯一值 set给grade. 要不肯定出错的。
SET grade=()就不执行多条记录,而是报错呢?
我从另外一个角度说说吧子查询分关联子查询和非关联子查询两种非关联子查询的话
子查询一次就执行完毕了关联子查询
因为子查询中要用到外面父查询的记录(where a.id = students.id ,你这里就用到了外层的那个students表的id字段),所以对应于外层表中的每一条记录都要执行一次子查询
你就把它理解成你那个upate就可以了
外层语句就行 不一定是查询
单独写一个UPDATE students要这样写UPDATE students
SET grade='';这里就不要括号了.