有这样一个表 user(id,name,salary),要查找的结果是如果工资低于2000则显示underpaid,高于4000则显示overpaid,在2者之间显示ok.
1 select * from user
结果:
id name salary
1 jack 2000
2 tony 5000
3 cahy 3800
4 lisa 1800
2 想要得到上面的查询结果,查询语句:
select id,name , salary,
case when salary<=2000 then 'underpaid'
when salary>4000 then 'overpaid'
else 'ok' end as status
from user
结果:
id name salary status
1 jack 2000 underpaid
2 tony 5000 overpaid
3 cahy 3800 ok
4 lisa 1800 underpaid