执行sql:select node_ids from t_node_drug_dir where drug_code = 68
结果为:1,4,5,6
问题:现需判断id是否在node_ids字符串中
思路:需要把node_ids转换为数组,然后判断id是否在数组中
使用regexp_split_to_array函数对字符串进行截取:SELECT regexp_split_to_array('kenyon,love,,china,!',',');
结果为:"{kenyon,love,"",china,!}"
方法一:
select * from t_node
where id in (
select unnest((select (regexp_split_to_array(node_ids,','))::integer[] from t_node_drug_dir where drug_code = 68))
)
方法二:
select * from t_node
where (
(select (regexp_split_to_array(node_ids,','))::integer[] from t_node_drug_dir where drug_code = 68)
) @> array[id]
方法三:
select * from t_node
where id =
any (
(select (regexp_split_to_array(node_ids,',')) dd from t_node_drug_dir where drug_code = 68)::integer[]
)
但如下写法不行:
select * from idms.t_node
where id =
any (
(select (regexp_split_to_array(to_node_ids,','))::integer[] dd from yk.t_node_drug_dir where drug_code = 6894)
)
参考资料:
http://www.csdn123.com/html/blogs/20130523/15615.htm
http://www.cnblogs.com/gaojian/p/3190737.html |