python case when用法_case when

# 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

```