oracle like问题！！！！

Question

我有个select语句sql1 : select * from abc where id like 'dddd%'其中dddd是我sql2 : select id from ccc 的结果集例如：如果我sql2的结果是('22','33','44')
          sql1就是 select * from abc where id like 22，或者like 33，或者like 44like有没有类似于 select id in('aa','bb','cc')的方法？
我有没有办法将两个sql语句写到一起？

Answer 1

前段时候有人问过
还想要简单点
几种方法
一种是用OR LIKE
一种是把这个字符串分拆成一个结果集
然后再比较

Answer 2

看此贴，和你的问题一样的
http://topic.csdn.net/u/20080829/15/d74ed512-8307-4a7b-8977-f147201b98ea.html

Answer 3

select a.*
from tableName A ,(SELECT     DECODE (INSTR (:in_str, ',', 1, 1),
                              0, :in_str,
                              SUBSTR (:in_str,
                                      DECODE (ROWNUM,
                                              1, 1,
                                                INSTR (:in_str,
                                                       ',',
                                                       1,
                                                       ROWNUM - 1
                                                      )
                                              + 1
                                             ),
                                      DECODE (ROWNUM,
                                              1, INSTR (:in_str, ',', 1, 1) - 1,
                                                LENGTH (:in_str)
                                              - LENGTH (REPLACE (:in_str,
                                                                 ',',
                                                                 ''
                                                                )
                                                       )
                                              + 1, LENGTH (:in_str),
                                                INSTR (:in_str, ',', 1, ROWNUM)
                                              - 1
                                              - INSTR (:in_str,
                                                       ',',
                                                       1,
                                                       ROWNUM - 1
                                                      )
                                             )
                                     )
                             ) in_str
                 FROM DUAL
           CONNECT BY ROWNUM <=
                           LENGTH (:in_str)
                         - LENGTH (REPLACE (:in_str, ',', ''))
                         + 1) b
                         where a.code like b.in_str||'%'

Answer 4

ls的方法不错，就是太麻烦了，你还不如写成两个sql先select出来,装到一个数组里a[]然后用个循环select * from abc where id like 'a[0]%'
for(int i=1;i<a.length;i++){
   or like 'a[i]%'
}当然就是大概这个意思，这么写当然不对，改一下

Answer 5

试试这个，看是不是你想要的
 select abc.* from abc,ccc where substr(abc.id,1,length(ccc.id))=ccc.id 

Answer 6

以前碰到过这种情况
用这个函数可以实现ALTER         FUNCTION cstr(@TparaStr VARCHAR(8000))
RETURNS @TmpTab TABLE(ListStr varchar(8000))  
AS
BEGINDECLARE @m VARCHAR(8000)
DECLARE @i INT
DECLARE @Y INT
DECLARE @N VARCHAR(8000)
DECLARE @Item VARCHAR(8000)
DECLARE @GROUPid varchar(8000)
DECLARE @TPARA VARCHAR(8000)
-- select @GROUPid=isnull(pname,'') from tpara where tbname='费用基数' and pno =@TparaStr
set @GROUPid=@TparaStr
-- SET @GROUPid = @TparaStr
SET @I  =  0
SET @Y  = 0
SET @Item  = ''
set @n = ''
set @m = ''
WHILE @Y <= LEN(@GROUPid)
BEGIN
IF SUBSTRING(@GROUPid,@Y,1) =','
BEGIN
SET @M = @Y
END
IF @M <> 0
BEGIN
       SET @item = SUBSTRING(@GROUPid, @n + 1, @m - 1 - @n)
       SET @n = @m
       SET @m = 0
       INSERT into @TmpTab(ListStr)values(@item)
END
   SET @Y = @Y + 1
   IF @y > LEN(@GROUPid) 
      BREAK
   ELSE
      CONTINUE
END

RETURN 
END

Answer 7

楼上的一看就知道是SQL SERVER的写法，可楼主问的是ORACLE

Answer 8

采用了蜗牛的方法，十分感谢hebo2005