ORACLE in (字符串,字符串,字符串)

因为传进来的参数是 字符串,字符串,字符串，要实现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是第几次出现。