采用SQL2000数据库
stuMarks表中writtenExam有2条记录:
ID writtenExam
1 80
2 30
执行以下语句:
IF exists (select * from stuMarks where writtenExam >60)
BEGIN
print '笔试成绩高于60分,每人+10分,加分后的成绩为:'
update stuMarks set writtenExam=writtenExam +10
select * from stuMarks
END
ELSE
BEGIN
print '笔试成绩太低需要+30分,加分后的成绩为'
update stuMarks set writtenExam=writtenExam +30
select * from stuMarks
ENDGO为什么ELSE部分没有执行?反而是都加10 最后stuMarks显示为: ID writtenExam
1 90
2 40请问错在哪里?好象EXISTS根本没有执行检查
stuMarks表中writtenExam有2条记录:
ID writtenExam
1 80
2 30
执行以下语句:
IF exists (select * from stuMarks where writtenExam >60)
BEGIN
print '笔试成绩高于60分,每人+10分,加分后的成绩为:'
update stuMarks set writtenExam=writtenExam +10
select * from stuMarks
END
ELSE
BEGIN
print '笔试成绩太低需要+30分,加分后的成绩为'
update stuMarks set writtenExam=writtenExam +30
select * from stuMarks
ENDGO为什么ELSE部分没有执行?反而是都加10 最后stuMarks显示为: ID writtenExam
1 90
2 40请问错在哪里?好象EXISTS根本没有执行检查
BEGIN
print '笔试成绩高于60分,每人+10分,加分后的成绩为:'
update stuMarks set writtenExam=writtenExam +10
select * from stuMarks
END
ELSE IF exists (select * from stuMarks where writtenExam <=60)
BEGIN
print '笔试成绩太低需要+30分,加分后的成绩为'
update stuMarks set writtenExam=writtenExam +30
select * from stuMarks
END GO
BEGIN
print '笔试成绩高于60分,每人+10分,加分后的成绩为:'
update stuMarks set writtenExam=writtenExam +10
select * from stuMarks
END
IF exists (select * from stuMarks where writtenExam <=60)
BEGIN
print '笔试成绩太低需要+30分,加分后的成绩为'
update stuMarks set writtenExam=writtenExam +30
select * from stuMarks
END GO 多了一个else
UPDATE stuMarks SET
writtenExam=writtenExam+CASE WHEN writtenExam>60 THEN 10 ELSE 30 END
--用不着写if else
update stuMarks SET writtenExam=writtenExam+(CASE WHEN writtenExam>60 THEN 10 ELSE 30 END)
GO
insert @t select 1,80
union all select 2,30select * from @t
/*(2 行受影响)
id writtenExam
----------- -----------
1 80
2 30(2 行受影响)
*/update @t
set writtenExam=writtenExam+
(case when writtenExam>60 then 10 else 30 end)select * from @t
/*
(2 行受影响)id writtenExam
----------- -----------
1 90
2 60(2 行受影响)
*/