业务场景:
需要查询A中心2016出生得小孩,B中心2015出生得小孩,C中心2014出生得小孩
常规写法:
写法一:
SELECT * FROM t_child WHERE center_id = ‘A‘ AND birth_year = 2016 union all
SELECT * FROM t_child WHERE center_id = ‘B‘ AND birth_year = 2015 union all
SELECT * FROM t_child WHERE center_id = ‘C‘ AND birth_year = 2014;
写法二:
SELECT * FROM t_child WHERE (center_id=‘A‘ AND birth_year = 2016)
OR (center_id = ‘B‘ AND birth_year = 2015)
OR (center_id = ‘C‘ AND birth_year = 2014);
这是当条件个数比较少的时候是适用的,且语句不适合于动态拼接条件,如果用In的话就可以解决以上两个问题了。
使用组合IN的写法:
SELECT * FROM t_child WHERE (center_id,birth_year ) in ( (‘A‘,2016), (‘B‘,2015), (‘C‘,2014) )