select to_number('100000000',999999999.99) from scott.zlcsb; TO_NUMBER('100000000',999999999.99) ----------------------------------- 100000000select to_char(zldm,999999999.99) from scott.zlcsb; TO_CHAR(ZLDM, ------------- 1.00都可以呀。
to_number(表字段名(varchar),'999999999'.99), sqlplus下试一下: select to_number('1234') from dual; select to_number('1234.56','9999.99') from dual; select to_number('123456','9999.99') from dual;
如果字段存储的是ABCD,可能会有这个错误吧? 请测试
select to_number('1234.56','9999.99') from dual;这么写是没有错误的,可是,如果这么写insert into table_A(a,b) select a,sum(to_number(b,'9999999999999999.99)) from table_A where c='1' group by a / 这样是有错误的,返回的是语法错误,各位是否知道怎么解决呀,郁闷啊!如果这样不行,就只有写程序代码解决了:(
insert into table_A(a,b) select a,sum(to_number(b,'9999999999999999.99)) from table_A where c='1' group by a 改为: create table_B(a,b) select a,sum(to_number(b,'9999999999999999.99')) from table_A where c='1' group by a;
insert into table_A(a,b) select a,sum(to_number(b,'9999999999999999.99)) from table_A where c='1' group by a 的错误之处: 1.表table_A表中已有字段a,而你又去插入,显然只能修改,而不能重复插入; 2. .99少了右单引号。还是采用创建一个table_B表的方法(如上)比较合适。
SQL> select * from aa;ID FID -- --- 1 0 2 1 3 1 4 2 5 3 6 4 6 5SQL> select id,sum(to_number(fid,'9999999999999999.99')) from aa group by id;ID SUM(TO_NUMBER(FID,'99999999999 -- ------------------------------ 1 0 2 1 3 1 4 2 5 3 6 9
-----------------------------------
100000000select to_char(zldm,999999999.99) from scott.zlcsb;
TO_CHAR(ZLDM,
-------------
1.00都可以呀。
sqlplus下试一下:
select to_number('1234') from dual;
select to_number('1234.56','9999.99') from dual;
select to_number('123456','9999.99') from dual;
请测试
select a,sum(to_number(b,'9999999999999999.99)) from table_A where c='1'
group by a
/
这样是有错误的,返回的是语法错误,各位是否知道怎么解决呀,郁闷啊!如果这样不行,就只有写程序代码解决了:(
select a,sum(to_number(b,'9999999999999999.99)) from table_A where c='1'
group by a
改为:
create table_B(a,b)
select a,sum(to_number(b,'9999999999999999.99')) from table_A where c='1'
group by a;
select a,sum(to_number(b,'9999999999999999.99)) from table_A where c='1'
group by a
的错误之处:
1.表table_A表中已有字段a,而你又去插入,显然只能修改,而不能重复插入;
2. .99少了右单引号。还是采用创建一个table_B表的方法(如上)比较合适。
-- ---
1 0
2 1
3 1
4 2
5 3
6 4
6 5SQL> select id,sum(to_number(fid,'9999999999999999.99')) from aa group by id;ID SUM(TO_NUMBER(FID,'99999999999
-- ------------------------------
1 0
2 1
3 1
4 2
5 3
6 9