where条件一个clob类型的字段like多次的问题

非著名魔兽解说 发布于 2016/10/18 15:33
阅读 310
收藏 0

@IdleMan 你好,想跟你请教个问题:

如下语句可以将一个字符串按照逗号拆分成多个部分,每一个被逗号分隔的部分都是where条件的一部分,比如:

SELECT REGEXP_SUBSTR('man,peter,北京,25', '[^,]+', 1, LEVEL, 'i') item
       from dual connect by rownum <= regexp_count('man,peter,北京,25',',') + 1



结果为:
man
peter
北京
25

需要将以上4个值放入where条件,

select * from Table1  t1  where t1.beizhu like '%man%' and t1.beizhu like '%peter%'
and t1.beizhu like '%北京%' and t1.beizhu like '%25%'





请问这个要怎么实现?

加载中
0
IdleMan
IdleMan


WITH Table1 AS
  ( SELECT 'man,peter,北京,25' beizhu FROM dual
  UNION
  SELECT 'man2,peter,北京,25' beizhu FROM dual
  UNION
  SELECT 'peter,北京,25' beizhu FROM dual
  )
SELECT *
FROM Table1 t1
WHERE EXISTS
  (SELECT NULL
  FROM
    (SELECT REGEXP_SUBSTR('man,peter,北京,25', '[^,]+', 1, LEVEL, 'i') item,regexp_count('man,peter,北京,25',',') + 1 cnt
    FROM dual
      CONNECT BY rownum <= regexp_count('man,peter,北京,25',',') + 1
    ) t2
  WHERE t1.beizhu LIKE '%'
    || t2.item
    || '%'
  GROUP BY t1.beizhu
  HAVING COUNT(*)=min(cnt)
  );




确定是你期望的?

0
IdleMan
IdleMan

clob不支持group,可以group by t1.id

0
非著名魔兽解说
非著名魔兽解说


引用来自“IdleMan”的评论


WITH Table1 AS
  ( SELECT 'man,peter,北京,25' beizhu FROM dual
  UNION
  SELECT 'man2,peter,北京,25' beizhu FROM dual
  UNION
  SELECT 'peter,北京,25' beizhu FROM dual
  )
SELECT *
FROM Table1 t1
WHERE EXISTS
  (SELECT NULL
  FROM
    (SELECT REGEXP_SUBSTR('man,peter,北京,25', '[^,]+', 1, LEVEL, 'i') item,regexp_count('man,peter,北京,25',',') + 1 cnt
    FROM dual
      CONNECT BY rownum <= regexp_count('man,peter,北京,25',',') + 1
    ) t2
  WHERE t1.beizhu LIKE '%'
    || t2.item
    || '%'
  GROUP BY t1.beizhu
  HAVING COUNT(*)=min(cnt)
  );




确定是你期望的?

我修改了一下代码,想要按照kate,man2去做模糊查询,如下:

WITH Table1 AS
  ( SELECT 'man,peter,北京,25' beizhu FROM dual
  UNION
  SELECT 'man2,peter,北京,25' beizhu FROM dual
  UNION
  SELECT 'kate,北京,25' beizhu FROM dual
  )
SELECT *
FROM Table1 t1
WHERE EXISTS
  (SELECT NULL
  FROM
    (SELECT REGEXP_SUBSTR('kate,man2', '[^,]+', 1, LEVEL, 'i') item,regexp_count('kate,man2',',') + 1 cnt
    FROM dual
      CONNECT BY rownum <= regexp_count('kate,man2',',') + 1
    ) t2
  WHERE t1.beizhu LIKE '%'
    || t2.item
    || '%'
  GROUP BY t1.beizhu
  HAVING COUNT(*)=min(cnt)
  );

应该查询出


man2,peter,北京,25
kate,北京,25
这2条,但是查询出的结果是空,请问我哪里写的有问题?
IdleMan
IdleMan
>0
IdleMan
IdleMan
如果换成or,那count>1即可
IdleMan
IdleMan
为啥是两条,你的条件是and,同时存在kate,man2的项本来就没有
返回顶部
顶部