表数据如下: 姓名 成绩 a 80 b 90 c 56 d 44 e 55 f D h A i C j 就卡斯两列都是varchar型 如何获取 成绩 > 80 的 姓名和成绩 要求应该只显示 “b 90”
select * from table_name where 成绩>80 and translate(成绩,'@1234567890','@') is null ;记得where后2个条件的顺序不可以反,一般oracle的where条件是从右向左解析的。 SQL> select 1 2 from dual 3 where 'aa'>80 and translate('aa','@123456789','@') is null 4 ;no rows selectedSQL> select 1 2 from dual 3 where translate('aa','@123456789','@') is null and 'aa'>80 4 ; where translate('aa','@123456789','@') is null and 'aa'>80 * ERROR at line 3: ORA-01722: invalid number
我原来用的是 select * from table_name where to_number(成绩) > 80提示invalid number错误
with temp as (select 'A' 姓名,'90' 成绩 from dual union all select 'B','A' from dual union all select 'C','我是一个' from dual ) select 姓名,max(成绩) from temp where ascii(成绩)<65 and ascii(成绩)>48 group by 姓名 having max(成绩)>80--result: A 90
with temp as (select 'A' 姓名,'90' 成绩 from dual union all select 'B','A' from dual union all select 'C','我是一个' from dual ) select 姓名,max(成绩) from temp where translate(成绩,'@1234567890','@') is null group by 姓名 having max(成绩)>80--RESULT: A 90
100分用3位数显示吧?那么'100'是<'80'的 如果以数字开头的不会混有字符 试试这个 select * from tt where 成绩 between '0' and '999' and to_number(成绩)>80
或者 select * from tt where 成绩 between '80' and '99' or 成绩='100'
考虑到分数有小数点的情况,加了个点 select * from table_name where 成绩>80 and translate(成绩,'@1234567890.','@') is null ;
为什么
SELECT * FROM (SELECT * FROM T2 WHERE TRANSLATE(成绩,'@1234567890.','@') IS NULL) a WHERE TO_NUMBER(a.成绩)>80 这样也会提示无效的数字呢
如果为数字的话 translate(column_name,'@1234567890.','@') is null 成立 得到可以转化为数字的数据 select * from (select * from table_name where translate(column_name,'@1234567890.','@') is null ) t where to_number(t.column_name) >80
有一种也是不行的,比如'.','....'这种数据。 所以translate(column_name,'@1234567890.','@') is null and rtrim(column_name,'.') is not null 这种条件才算完美。 select * from table_name where 成绩>80 and translate(成绩,'@1234567890.','@') is null and rtrim(column_name,'.') is not null ;
考虑到多个小数点的情况 select * from table_name where 成绩>80 and translate(成绩,'@1234567890.','@') is null and rtrim(成绩,'.') is not null and instr(成绩,'.',1,2)=0 ;
貌似我补充了 不会连判断点都写不出吧 ? select * from (select * from table_name where translate(column_name,'@1234567890.','@') is null and instr(column_name,'.',1,2)=0 ) t where to_number(t.column_name) >80 这样了 OK?
楼上的 先用数据试下 100 还有 有了instr 就不要那个了 多次一举
前面点 和后面点 有问题吗 前面点 就0.几吧 后面点就自动去掉了 两个点或以上instr就够了
select name ,grade from stu where ascii(grade) between 48 and 57 and to_number(grade) between 80 and 100;
姓名 成绩
a 80
b 90
c 56
d 44
e 55
f D
h A
i C
j 就卡斯两列都是varchar型
如何获取 成绩 > 80 的 姓名和成绩 要求应该只显示 “b 90”
from table_name
where 成绩>80 and translate(成绩,'@1234567890','@') is null
;记得where后2个条件的顺序不可以反,一般oracle的where条件是从右向左解析的。
SQL> select 1
2 from dual
3 where 'aa'>80 and translate('aa','@123456789','@') is null
4 ;no rows selectedSQL> select 1
2 from dual
3 where translate('aa','@123456789','@') is null and 'aa'>80
4 ;
where translate('aa','@123456789','@') is null and 'aa'>80
*
ERROR at line 3:
ORA-01722: invalid number
select * from table_name where to_number(成绩) > 80提示invalid number错误
as
(select 'A' 姓名,'90' 成绩 from dual
union all
select 'B','A' from dual
union all
select 'C','我是一个' from dual
)
select 姓名,max(成绩) from temp where ascii(成绩)<65 and ascii(成绩)>48
group by 姓名
having max(成绩)>80--result:
A 90
as
(select 'A' 姓名,'90' 成绩 from dual
union all
select 'B','A' from dual
union all
select 'C','我是一个' from dual
)
select 姓名,max(成绩) from temp where translate(成绩,'@1234567890','@') is null
group by 姓名
having max(成绩)>80--RESULT:
A 90
如果以数字开头的不会混有字符
试试这个
select * from tt
where 成绩 between '0' and '999' and to_number(成绩)>80
select * from tt
where 成绩 between '80' and '99' or 成绩='100'
select *
from table_name
where 成绩>80 and translate(成绩,'@1234567890.','@') is null
;
SELECT *
FROM (SELECT * FROM T2 WHERE TRANSLATE(成绩,'@1234567890.','@') IS NULL) a
WHERE TO_NUMBER(a.成绩)>80
这样也会提示无效的数字呢
select * from
(select * from table_name where translate(column_name,'@1234567890.','@') is null ) t
where to_number(t.column_name) >80
有一种也是不行的,比如'.','....'这种数据。
所以translate(column_name,'@1234567890.','@') is null and rtrim(column_name,'.') is not null 这种条件才算完美。
select *
from table_name
where 成绩>80 and translate(成绩,'@1234567890.','@') is null and rtrim(column_name,'.') is not null
;
select *
from table_name
where 成绩>80 and translate(成绩,'@1234567890.','@') is null and rtrim(成绩,'.') is not null and instr(成绩,'.',1,2)=0
;
?
select * from
(select * from table_name where translate(column_name,'@1234567890.','@') is null and instr(column_name,'.',1,2)=0 ) t
where to_number(t.column_name) >80
这样了 OK?