在oracle数据库表中,我需要找到给定批号的结果.
保存批号的字段是包含类似’1-3,5,10-15,20’的字符串(此字符串中的数字已排序)
保存批号的字段是包含类似’1-3,5,10-15,20’的字符串(此字符串中的数字已排序)
有没有办法做到这一点？
在上面的示例中,应找到以下批号的结果：
1,2,3,10,11,12,13,14,15,20
在应用程序中无法执行此操作,因此必须在数据库中完成.
类似于：“SELECT * FROM products WHERE lot = 2”
解决方法
可以使用
REGEXP_SUBSTR函数和
hierarchical queries在sql中完成所有操作：
with list_of_ids as (
select regexp_substr(a,'[[:digit:]]+',1,1) as lot1,nvl( regexp_substr(a,'(-)([[:digit:]]+)','i','2'),regexp_substr(a,1)) as lot2
from (select regexp_substr('1-3,20','[^,]+',level) as a
from dual
connect by regexp_substr('1-3,level) is not null
)
)
select a.*
from products a
join list_of_ids b
on a.lot between b.lot1 and b.lot2
但是,我必须强调正确地规范化数据库是要走的路.这种解决方案可能无法很好地扩展,并且可以完成大量不必要的工作.
它的工作原理如下：
首先在逗号上拆分数据：
sql> select regexp_substr('1-3,level) as a 2 from dual 3 connect by regexp_substr('1-3,level) is not null 4 ; A -------------- 1-3 5 10-15 20
接下来,将其拆分为连字符,以便在最终将其连接到表之前提供BETWEEN中使用的最小和最大批次. NVL用于确保始终存在最大值.
sql> select regexp_substr(a,1) as lot1 2,'2') 3,1)) as lot2 4 from (select regexp_substr('1-3,level) as a 5 from dual 6 connect by regexp_substr('1-3,level) is not null 7 ) 8 ; LOT1 LOT2 -------------- -------------- 1 3 5 5 10 15 20 20 sql>
这是一个有完整查询的SQL Fiddle工作.
