with temp as( select 1 id,'a' tt from dual union all select 2 id,'b' tt from dual union all select 11 id,'c' tt from dual union all select 123 id,'c' tt from dual ) select * from temp where length(id) <=1
with test as( select 1 id,'a' tt from dual union all select 2 id,'b' tt from dual union all select 334 id,'c' tt from dual union all select 122 id,'d' tt from dual) select * from test where length(id) <=1 ID T ---------- - 1 a 2 b
---没有空格 select id,tt from tb where length(id)=1 ---有空格select id,tt from tb where length(trim(' ' from id))=1
晕,跟我这样的查询语句 select yskmdm,lrr from pd_xmxx where length(yskmdm)<=5 一样的呀,语句是没有错误,但是查询不到数据,其实是应该有数据的。
select length(yskmdm),yskmdm,lrr from pd_xmxx --看看这个语句是什么结果 你分析下
如果 yskmdm 列为数值类型 select * from pd_xmxx where yskmdm/100000<1;如果 yskmdm 列为字符类型 select * from pd_xmxx where length(trim(yskmdm))<=5;
那就是应该有空格 用下面的语句试试 你这个字段定义的什么类型? select length(trim(yskmdm)),yskmdm,lrr from pd_xmxx
select *from test where id<=2
where length(id)<=1;
select 1 id,'a' tt from dual
union all
select 2 id,'b' tt from dual
union all
select 11 id,'c' tt from dual
union all
select 123 id,'c' tt from dual
)
select * from temp where length(id) <=1
with test as(
select 1 id,'a' tt from dual union all
select 2 id,'b' tt from dual union all
select 334 id,'c' tt from dual union all
select 122 id,'d' tt from dual)
select * from test where length(id) <=1 ID T
---------- -
1 a
2 b
select id,tt
from tb where length(id)=1
---有空格select id,tt
from tb where length(trim(' ' from id))=1
晕,跟我这样的查询语句
select yskmdm,lrr from pd_xmxx where length(yskmdm)<=5 一样的呀,语句是没有错误,但是查询不到数据,其实是应该有数据的。
--看看这个语句是什么结果 你分析下
select * from pd_xmxx where yskmdm/100000<1;如果 yskmdm 列为字符类型
select * from pd_xmxx where length(trim(yskmdm))<=5;
你这个字段定义的什么类型?
select length(trim(yskmdm)),yskmdm,lrr from pd_xmxx
yskmdm 是预算科目代码, lrr是预算单位在预算科目代码有5位数的,也有7位数的。我只要查询出预算科目代码是5位数的预算单位。如预算科目代码5位有20101 和7位的2010101
这样查询出来 length(trim(yskmdm)) 这列就有5 和7
from tb where length(replace(id,' ',''))<2
thanks。可以正确的查询了。
select yskmdm,lrr from pd_xmxx where length(to_number(yskmdm))=5如果有字符的话用
select yskmdm,lrr from pd_xmxx where length(trim(yskmdm))=5
from test
where length(trim(id))<=1;估计有空字符