我原来的sql语句类似select case when instr(zpz,'/')=0 then nvl(zpz,0) else to_char((substr(zpz,1,INSTR(zpz,'/')-1)+substr(zpz,INSTR(zpz,'/')+1))/2) end from table1 来实现的,可是现在的情况是"/"在字段中可能出现若干次如20/22/24
比如对于表aaa(a,b都为字符串),记录如下: a b 1 str1 1 str2 1 str3 2 sttr1 2 sttr2 现在要用一个SQL检索出如下的效果. a b 1 str1+str2+str3 2 sttr1+sttr2 要做到这样的效果可以用如下的SQL来实现: select a,ltrim(result,'+') as b (select a,max(sys_connect_by_path(b,'+')) result from (select a,b,(row_number() over(order by a,b desc)+dense_rank() over(order by a)) rn,max(b) over(partition by a) qs from aaa) start with b=qs connect by prior rn=rn-1 group by a)
谢谢sbaz的帮助,可是我这个项目要求是用sql语句的 :|
把计算这个字段的值写成一个函数,结果作为出参,然后再Sql语句里面调用该函数select func(str) as res from table;
才分了2个,这招太笨。。 select (case when instr(zpz,'/')=0 then nvl(zpz,0) when INSTR(zpz,'/')<=length(zpz) then substr(zpz,1,INSTR(zpz,'/')-1) end) as a1,
(case when INSTR(substr(zpz,INSTR(zpz,'/')+1),'/')<=length(zpz) then substr(zpz,INSTR(zpz,'/')+1,INSTR(substr(zpz,INSTR(zpz,'/')+1),'/')-1) end) as a2,
a b
1 str1
1 str2
1 str3
2 sttr1
2 sttr2
现在要用一个SQL检索出如下的效果.
a b
1 str1+str2+str3
2 sttr1+sttr2 要做到这样的效果可以用如下的SQL来实现:
select a,ltrim(result,'+') as b
(select a,max(sys_connect_by_path(b,'+')) result
from (select a,b,(row_number() over(order by a,b desc)+dense_rank()
over(order by a)) rn,max(b) over(partition by a) qs from aaa)
start with b=qs
connect by prior rn=rn-1
group by a)
(case when instr(zpz,'/')=0 then
nvl(zpz,0)
when INSTR(zpz,'/')<=length(zpz) then
substr(zpz,1,INSTR(zpz,'/')-1)
end) as a1,
(case
when INSTR(substr(zpz,INSTR(zpz,'/')+1),'/')<=length(zpz) then
substr(zpz,INSTR(zpz,'/')+1,INSTR(substr(zpz,INSTR(zpz,'/')+1),'/')-1)
end) as a2,
..... as a3
from table
SELECT SUM (a)
FROM (SELECT ROWNUM,
TO_NUMBER (SUBSTR ('/' || str || '/',
INSTR ('/' || str || '/', '/', 1, ROWNUM)
+ 1,
INSTR ('/' || str || '/',
'/',
1,
ROWNUM + 1
)
- INSTR ('/' || str || '/', '/', 1, ROWNUM)
- 1
)
) a
FROM tabtest
WHERE ROWNUM <=
( LENGTH ('/' || str || '/')
- LENGTH (REPLACE ('/' || str || '/', '/', ''))
))
我不明白您的意思啊
思路是先找一个表,只用他的rownum(为了把字符串拆开,所以表的记录要比拆开后的记录多才行),然后计算有多少个数,把它拆开,再求和。
FROM (SELECT ROWNUM,
TO_NUMBER (SUBSTR ('/' || zpz || '/',
INSTR ('/' || zpz || '/', '/', 1, ROWNUM)
+ 1,
INSTR ('/' || zpz || '/',
'/',
1,
ROWNUM + 1
)
- INSTR ('/' || zpz || '/', '/', 1, ROWNUM)
- 1
)
) a
FROM sjfzsj
WHERE ROWNUM <=
( LENGTH ('/' || zpz || '/')
- LENGTH (REPLACE ('/' || zpz || '/', '/', ''))) and ny='200504' and jh like'赵安%'
)sjfzsj中的记录足够多,可还是不行
例如你要查询的字符串字段str是从表sjfzsj中得到的,那应该
select
(SELECT SUM (a)
FROM (SELECT ROWNUM,
TO_NUMBER (SUBSTR ('/' || b.str || '/',
INSTR ('/' || b.str || '/', '/', 1, ROWNUM)
+ 1,
INSTR ('/' || b.str || '/',
'/',
1,
ROWNUM + 1
)
- INSTR ('/' || b.str || '/', '/', 1, ROWNUM)
- 1
)
) a
FROM tabtest
WHERE ROWNUM <=
( LENGTH ('/' || b.str || '/')
- LENGTH (REPLACE ('/' || b.str || '/', '/', ''))
))
)
from sjfzsj b where ny='200504' and jh like'赵安%'