因为传进来的参数是 字符串,字符串,字符串,要实现in(字符串,字符串,字符串)
select * from htl_price p where p.hotel_id = 30073328
and p.able_sale_date between to_date('2009-03-27','YYYY-MM-DD') and to_date('2009-03-28','YYYY-MM-DD')
and p.pay_method = 'pre_pay'
and to_char(p.child_room_type_id) in (33153,33154);
--目标是这样
select * from htl_price p where p.hotel_id = 30073328
and p.able_sale_date between to_date('2009-03-27','YYYY-MM-DD') and to_date('2009-03-28','YYYY-MM-DD')
and p.pay_method = 'pre_pay'
and to_char(p.child_room_type_id) in concat(concat('(','33153,33154'),')');
--此法不行
select * from htl_price p where p.hotel_id = 30073328
and p.able_sale_date between to_date('2009-03-27','YYYY-MM-DD') and to_date('2009-03-28','YYYY-MM-DD')
and p.pay_method = 'pre_pay'
and to_char(p.child_room_type_id) in
(select *
from (select regexp_substr('33153,33154', '[^,]+', 1, rownum) n
from dual
connect by rownum < 50)
where n is not null);
--这样就可以
connect by 把每次执行只返回一个结果组成一个结果集。
regexp_substr('33153,33154', '[^,]+', 1, rownum):参数1是从最头那个字母开始,rownum是第几次出现。