现有这样的两个字段存在于ml表中
min_limit max_limit
<5
>10
2 18
9
10
<=30现在要把这两个字段整合成一个字段limit
limit
<5
>10
2~18
9
10 <=30我用的方法是(oracle中):select (case when ml.min_limit is null and ml.max_limit is not null then ml.max_limit
when ml.min_limit is not null and ml.max_limit is null then ml.min_limit
when ml.min_limit is null and ml.max_limit is null then null
when ml.min_limit is not null and ml.max_limit is not null then ml.min_limit||'~'||ml.max_limit end) as limit
from mlp_com ml但出来的结果却是
limit
<5~
~>10
2~18
9~
~10
~
<=30~分析的原因大概是因为ml表中的那些空的数据,不算是null,所以一直执行的都是两个不为null的,都在中间加~。
那这个问题要如何解决啊?
求教各位大大啊!!!
min_limit max_limit
<5
>10
2 18
9
10
<=30现在要把这两个字段整合成一个字段limit
limit
<5
>10
2~18
9
10 <=30我用的方法是(oracle中):select (case when ml.min_limit is null and ml.max_limit is not null then ml.max_limit
when ml.min_limit is not null and ml.max_limit is null then ml.min_limit
when ml.min_limit is null and ml.max_limit is null then null
when ml.min_limit is not null and ml.max_limit is not null then ml.min_limit||'~'||ml.max_limit end) as limit
from mlp_com ml但出来的结果却是
limit
<5~
~>10
2~18
9~
~10
~
<=30~分析的原因大概是因为ml表中的那些空的数据,不算是null,所以一直执行的都是两个不为null的,都在中间加~。
那这个问题要如何解决啊?
求教各位大大啊!!!
when ml.min_limit is not null and ml.max_limit is null then ml.min_limit
when ml.min_limit is null and ml.max_limit is null then null
when TRIM(ml.min_limit) is not null and TRIM(ml.max_limit) is not null then ml.min_limit || '~ ' || ml.max_limit end) as limit
from mlp_com ml
select (case when ml.min_limit is null and ml.max_limit is not null then ml.max_limit
when ml.min_limit is null and ml.max_limit is null then null
when inchar(ml.min_limit,'<',1)>0 then ml.min_limit
when inchar(ml.max_limit,'>',1)>0 then ml.max_limit
when inchar(ml.min_limit,'<',1)=0 and inchar(ml.max_limit,'>',1)=0 then ml.min_limit ¦ ¦ '~ ' ¦ ¦ml.max_limit end) as limit
from mlp_com ml
其他种情况一律直接合并两个字段就OK!
select (case when TRIM(ml.min_limit) is null or TRIM(ml.max_limit) is not null ml.min_limit ¦ ¦ ml.max_limit
when TRIM(ml.min_limit) is not null and TRIM(ml.max_limit) is not null then ml.min_limit ¦ ¦ '~ ' ¦ ¦ ml.max_limit end) as limit
from mlp_com ml
这种判断字符串字数的方法我也想过。
但为什么pl/sql中inchar是不合法的标识符啊?对这个inchar函数不是很了解啊,能讲解一下吗,inchar(a,'<',1)如果a这个字段中的字符串是小于1个的,那就应该=0,如果是大于1个的就应该是>0。是这个意思吗?
感谢啊!我是这样写的:
select ml.min_limit,ml.max_limit,(case
when trim(ml.min_limit) is null and trim(ml.max_limit) is not null then ml.max_limit
when trim(ml.min_limit) is not null and trim(ml.max_limit) is null then ml.min_limit
when trim(ml.min_limit) is null and trim(ml.max_limit) is null then null
when ml.min_limit is not null and ml.max_limit is not null then ml.min_limit | | '~ ' | |ml.max_limit end) as limit
from mlp_values ml
你的分我马上解啊,不过先等一下,我想等elvis_gao说的那种用字符串的方法再试试看。你的分我是一定给的啊!
我从没有用过oracl,但是公司让我们将一些sql server的存储过程改成oracle的,所以看了一些oracle的语法,其中有:
sql server
select charindex('s','asdfasdf',2)
对应oracle
select instr('asdfasdf','s',2)
含义是 在字符串'asdfasdf'中查找第2个's'的位置值,返回6
你说的这个是查找字符串中的字母,那象instr(ml.max_limit,'<',1)=0这个是不是查找ml.nax_limit中字符个数小于1是假的,就是说这个是空的意思啊!