# 案例：改写SQL消除in-clause

#### 现象描述

in-clause/any-clause是常见的SQL语句约束条件，有时in或any后面的clause都是常量，类似于：

 ```1 2 3 4``` ```select count(1) from calc_empfyc_c1_result_tmp_t1 where ls_pid_cusr1 in (‘20120405’, ‘20130405’); ```

 ```1 2 3 4``` ```select count(1) from calc_empfyc_c1_result_tmp_t1 where ls_pid_cusr1 in any(‘20120405’, ‘20130405’); ```

 ```1 2 3 4 5``` ```SELECT ls_pid_cusr1,COALESCE(max(round((current_date-bthdate)/365)),0) FROM calc_empfyc_c1_result_tmp_t1 t1,p10_md_tmp_t2 t2 WHERE t1.ls_pid_cusr1 = any(values(id),(id15)) GROUP BY ls_pid_cusr1; ```

#### 优化说明

 ``` 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19``` ```select ls_pid_cusr1,COALESCE(max(round(ym/365)),0) from ( ( SELECT ls_pid_cusr1,(current_date-bthdate) as ym FROM calc_empfyc_c1_result_tmp_t1 t1,p10_md_tmp_t2 t2 WHERE t1.ls_pid_cusr1 = t2.id and t1.ls_pid_cusr1 != t2.id15 ) union all ( SELECT ls_pid_cusr1,(current_date-bthdate) as ym FROM calc_empfyc_c1_result_tmp_t1 t1,p10_md_tmp_t2 t2 WHERE t1.ls_pid_cusr1 = id15 ) ) GROUP BY ls_pid_cusr1; ```

*必选