case when函数

  sre

mysql
两种表达,功能一样的:
简单Case函数
CASE sex
WHEN ‘1’ THEN ‘男’
WHEN ‘2’ THEN ‘女’
ELSE ‘其他’ END

–Case搜索函数(可条件匹配)
CASE WHEN sex = ‘1’ THEN ‘男’
WHEN sex = ‘2’ THEN ‘女’
ELSE ‘其他’ END

例如:
SELECT
CASE WHEN salary <= 500 THEN '1' WHEN salary > 500 AND salary <= 600 THEN '2' WHEN salary > 600 AND salary <= 800 THEN '3' WHEN salary > 800 AND salary <= 1000 THEN '4' ELSE NULL END salary_class SELECT country, SUM( CASE WHEN sex = '1' THEN population ELSE 0 END), --男性人口 SUM( CASE WHEN sex = '2' THEN population ELSE 0 END) --女性人口 FROM Table_A GROUP BY country; UPDATE Personnel SET salary = CASE WHEN salary >= 5000   THEN salary * 0.9
WHEN salary >= 2000 AND salary < 4600 THEN salary * 1.15 ELSE salary END; 注意:Case函数只返回第一个符合条件的值,当多个case条件有重叠时,剩下的Case部分将会被自动忽略。 在Case函数中Else部分的默认值是NULL。 http://blog.sina.com.cn/s/blog_4c538f6c01012mzt.html

LEAVE A COMMENT

Captcha Code