SQL> SELECT * FROM BAO_TEMP;NAME AGE
-------------------- ----------
A 20
B 21
C 22
D 34目前歷時: 00:00:00.32
SQL> SELECT TO_NUMBER(AVG(AGE),99999999.99) FROM BAO_TEMP;TO_NUMBER(AVG(AGE),99999999.99)
-------------------------------
24.25
-------------------- ----------
A 20
B 21
C 22
D 34目前歷時: 00:00:00.32
SQL> SELECT TO_NUMBER(AVG(AGE),99999999.99) FROM BAO_TEMP;TO_NUMBER(AVG(AGE),99999999.99)
-------------------------------
24.25
select class,to_number(avg(age),99999.99) from table_name group by class;
select to_number(avg(nl),9999.99) from stud_j30
*
ERROR 位于第 1 行:
ORA-01722: 无效数字如果出現上述的情況,可能在你的nl字段中有空值的存在
SQL> select to_number(avg(nl),9999.99) from stud_j30 where nl is not null;
SQL> select to_number(avg(nl),9999.99) from stud_j30;
select to_number(avg(nl),9999.99) from stud_j30
*
ERROR 位于第 1 行:
ORA-01722: 无效数字如果出現上述的情況,可能在你的nl字段中有空值的存在
SQL> select to_number(avg(nvl(nl,0)),9999.99) from stud_j30;
select * from stud_j30;XH XM CSNY JG DNO NL
-------- -------- ---------- -------------------- --- ----------
i3005124 胡明天 14-4月 -83 湖南长沙 D01 21
i3001134 唐明海 14-4月 -83 湖南长沙 D01 21
i3002124 张丽梅 13-4月 -84 湖南长沙 D01 20
i3003124 李有能 14-4月 -82 湖南长沙 D02 22
i3004124 王汉中 14-4月 -82 湖南长沙 D02 22
i3004164 高 强 14-4月 -83 D02 21
i3004184 SMITH 14-4月 -83 D03 21
i3005194 TOM HONE 14-4月 -82 D03 22
i3002164 张 三 14-4月 -82 D04 22
i3002174 李 四 14-4月 -83 D04 21
i3002184 王老五 13-4月 -84 D04 20XH XM CSNY JG DNO NL
-------- -------- ---------- -------------------- --- ----------
ti011402 郑美光 13-4月 -84 D01 20
i3011111
i3995124
已选择14行。
SQL> select to_number(avg(nl),9999.99) from stud_j30 where nl is not null;
select to_number(avg(nl),9999.99) from stud_j30 where nl is not null
*
ERROR 位于第 1 行:
ORA-01722: 无效数字.
DEAN CHAR(8),TEL CHAR(8) CHECK(SUBSTR(1,3)='887'),
PRIMARY KEY(DNO)
);INSERT INTO DEPT_j30 VALUES('D01','计算机系','王大明','8879626');
INSERT INTO DEPT_j30 VALUES('D02','物理系','张大奔','8879726');
INSERT INTO DEPT_j30 VALUES('D03','化学系','李水明','8879826');
INSERT INTO DEPT_j30 VALUES('D04','管理系','欧阳卫红','8879926');
INSERT INTO DEPT_j30 VALUES('D05','外语系','宽左右','8879526');
CREATE TABLE STUD_j30(XH CHAR(8) PRIMARY KEY,XM CHAR(8),CSNY DATE,
JG VARCHAR2(20),DNO CHAR(3) REFERENCES DEPT_j30(DNO)
);INSERT INTO STUD_j30 VALUES('i3005124','胡明天',SYSDATE-365*21,'湖南长沙','D01');
INSERT INTO STUD_j30 VALUES('i3001134','唐明海',SYSDATE-365*21,'湖南长沙','D01');
INSERT INTO STUD_j30 VALUES('i3002124','张丽梅',SYSDATE-365*20,'湖南长沙','D01');
INSERT INTO STUD_j30 VALUES('i3003124','李有能',SYSDATE-365*22,'湖南长沙','D02');
INSERT INTO STUD_j30 VALUES('i3004124','王汉中',SYSDATE-365*22,'湖南长沙','D02');
INSERT INTO STUD_j30 VALUES('i3004164','高 强',SYSDATE-365*21,' ','D02');
INSERT INTO STUD_j30 VALUES('i3004184','SMITH',SYSDATE-365*21,' ','D03');
INSERT INTO STUD_j30 VALUES('i3005194','TOM HONE',SYSDATE-365*22,' ','D03');
INSERT INTO STUD_j30 VALUES('i3002164','张 三', SYSDATE-365*22,' ','D04');
INSERT INTO STUD_j30 VALUES('i3002174','李 四', SYSDATE-365*21,' ','D04');
INSERT INTO STUD_j30 VALUES('i3002184','王老五', SYSDATE-365*20,' ','D04');
INSERT INTO STUD_j30 VALUES('ti011402','郑美光', SYSDATE-365*20,' ','D01'); alter table stud_j30 add NL number(2);
update stud_j30 set NL=(sysdate-csny)/365;
后来还插入了一些只有学号的信息。
SQL> SELECT TO_NUMBER('21.0833333',9999.99) from DUAL;
SELECT TO_NUMBER('21.0833333',9999.99) from DUAL
*
ERROR 在行 1:
ORA-01722: 數字無效
SQL> SELECT TO_NUMBER('21.08',9999.99) from DUAL;TO_NUMBER('21.08',9999.99)
--------------------------
21.08SQL> SELECT TO_NUMBER('21.0833333',9999.9999999) from DUALTO_NUMBER('21.0833333',9999.9999999)
------------------------------------
21.0833333SQL> SELECT SUBSTR('21.0833333',1,4) FROM DUAL;而
SQL> select
2 TO_CHAR(AVG(nl)) from stud_j30 where nl is not null;TO_CHAR(AVG(NL))
----------------------------------------
21.0833333333333333333333333333333333333
所以解決的方法是截取串長度
SQL> select to_number(substr(to_char(avg(nl)),1,5),999999.99)
2 from stud_j30 where nl is not null;TO_NUMBER(SUBSTR(TO_CHAR(AVG(NL)),1,5),999999.99)
-------------------------------------------------
21.08
這個我也不大清除了!
其實最簡單的解決方法是:
SQL> select round(avg(nl),2) from stud_j30 where nl is not null;ROUND(AVG(NL),2)
----------------
21.08