SQL7：CASE语句

--case用于不等的判断
--优化：低于60分的学生提示不及格
select tsname,
isnull(
case when tEnglish<60 then '不及格' else CAST(tEnglish as varchar(10)) end
,'缺考')
from TblStudent
left join TblScore on TblStudent.tSId=TblScore.tSId

--判断等的情况
use hem09

select * from Employee
update Employee set eGender=1 where eName='sk'

--将性别显示成男和女
select *,case egender when 0 then '男' when 1 then '女' end
from Employee

--有一个财务流水表
--MoneyFlow fid fTitle fMoney
create table MoneyFlow
(
fid int identity(1,1) primary key not null,
ftitle nvarchar(10),
fmoney money
)

select * from MoneyFlow
insert into MoneyFlow values('发工资',1000)
insert into MoneyFlow values('奖金',500)
insert into MoneyFlow values('捡钱',200)
insert into MoneyFlow values('请客',-1500)
insert into MoneyFlow values('洗脚',-3000)

select fid,ftitle,case when fmoney>0 then fmoney else 0 end as '收入',
case when fmoney<0 then ABS(fmoney) else 0 end as '支出'
from MoneyFlow