SQL 条件子句

1 case子句

SELECT  stock_name, sum(case when operation="Buy" then -price else price end) as capital_gain_loss
FROM Stocks 
GROUP BY stock_name;

语法格式为：

case 
when 条件 then 满足条件时的操作
when ... then ...
else 不满足条件时的操作
end

可以有多个when...then...语句。

例如：

select device_id, gender, 
case 
when age < 20 then '20岁以下' 
when age <= 24 then '20-24岁'
when age > 24 then '25岁及以上'
else '其他'
end 
as age_cut from user_profile;

2 if子句

if (condition, value_if_true, value_if_false)

其中condition为条件，value_if_true表示条件为真时的值/操作，value_if_false表示条件为假时的值/操作。

示例，

select employee_id, 
if 
(
    employee_id % 2 = 1 and name not like 'M%',
    salary,
    0
) as bonus
from employees
order by employee_id;