case when 用法:
case when expression1 then XXX when expression2 then XXX end;
例:select case when grade <60 then “不及格”
when grade >=60 then “及格”
end AS STATUS
from class;case column when A1 then XXX when A2 then XXX,eles XXX end;
例:select case grade when 60 then “刚好及格”
when 0 then “抓紧玩儿去”
else “都太平庸”
end AS FAIL
from class;也可以将case when 用在Where条件中(例子可能不大合适,只是说明用法)
例:#统计出属于low的罚款编号。重点看这里的解决方法
select paymentno,amount from penalties where case when amount>0 and amount<=40 then ‘low’ when amount>40 and amount<=80 then ‘moderate’ when amount>80 then ‘high’ else ‘incorrect’ end =’low’;