# case when的表达式形式
## 1. 简单Case函数
```
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
```
## 2. Case搜索函数
```
CASE
WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END
```
# 在不同位置的用法
## 1. select case when 用法
```
计算男女占比
SELECT
SUM(
CASE
WHEN sex=1 THEN 1
ELSE 0
END
) AS boy,
SUM(
CASE
WHEN sex=2 THEN 1
ELSE 0
END
) AS girl,
ROUND(boy/(boy+girl), 3) as rate1,
ROUND(girl/(boy+girl), 3) as rate2
FROM user_profile_all;
--------------------------------------
boy girl rate1 rate2
139220493 166724237 0.455 0.545
```
## 2. where case用法
```
SELECT T2.*, T1.*
FROM T1, T2
WHERE (
CASE
WHEN T2.COMPARE_TYPE = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%'
THEN 1
WHEN T2.COMPARE_TYPE != 'A' AND T1.SOME_TYPE NOT LIKE 'NOTHING%'
THEN 1
ELSE
0
END) = 1
```
## 3. group by case when用法
```
按照年龄分段统计
SELECT
CASE
WHEN yob >2001
THEN '18-'
WHEN yob <= 2001 AND yob > 1984
THEN '18-35'
WHEN yob <= 1984 AND yob != 0
THEN '35+'
ELSE
'未知'
END AS age,
COUNT() AS sum
FROM user_profile_all
GROUP BY
CASE
WHEN yob >2001
THEN '18-'
WHEN yob <= 2001 AND yob > 1984
THEN '18-35'
WHEN yob <= 1984 AND yob != 0
THEN '35+'
ELSE
'未知'
END;
--------------------------------------
age sum
18- 22479576
35+ 33509842
18-35 250019503
未知 582318559
```