SQL*Plus: Release 9.2.0.1.0 - Production on 星期六 8月 21 23:24:50 2010Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - ProductionSQL> create table t1(id number(21),name varchar2(20));表已创建。SQL> create table t2(id varchar2(21));表已创建。SQL> insert into t1 values(123456789012345678901,'abc');已创建 1 行。SQL> insert into t2 values('123456789012345678901');已创建 1 行。SQL> select * from t1,t2 where to_char(t1.id)=t2.id; ID NAME ID ---------- -------------------- --------------------- 1.2346E+20 abc 123456789012345678901SQL> select to_char(id),name from t1;TO_CHAR(ID) NAME ---------------------------------------- -------------------- 123456789012345678901 abcSQL>
连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - ProductionSQL> create table t1(id number(21),name varchar2(20));表已创建。SQL> create table t2(id varchar2(21));表已创建。SQL> insert into t1 values(123456789012345678901,'abc');已创建 1 行。SQL> insert into t2 values('123456789012345678901');已创建 1 行。SQL> select * from t1,t2 where to_char(t1.id)=t2.id; ID NAME ID
---------- -------------------- ---------------------
1.2346E+20 abc 123456789012345678901SQL> select to_char(id),name from t1;TO_CHAR(ID) NAME
---------------------------------------- --------------------
123456789012345678901 abcSQL>
试过 t1.id=t2.id 或to_char(t1.id)=t2.id 或t1.id=to_number(t2.id)都一样
SQL> create table tb1(id number,name varchar2(10));表已创建。SQL> insert into tb1 values(123456789012345678901,'wkc168');已创建 1 行。SQL> create table tb2(id2 varchar2(50),name varchar2(10));表已创建。SQL> insert into tb2 values('123456789012345678901','wkc28');已创建 1 行。SQL> commit;提交完成。
SQL> col a_id format 'fm99999999999999999999999999'
SQL>
SQL> select a.id a_id,a.name name1,b.name name2
2 from tb1 a,tb2 b
3 where a.id=to_number(b.id2)
4 / A_ID NAME1 NAME2
--------------------------- ---------- ----------
123456789012345678901 wkc168 wkc28SQL> select a.id a_id,b.id2 b_id,a.name name1,b.name name2
2 from tb1 a,tb2 b
3 where b.id2=to_char(a.id)
4 / A_ID B_ID NAME1 NAME2
--------------------------- ----------------------------------- ---------- ----------
123456789012345678901 123456789012345678901 wkc168 wkc28SQL>
插入值的时候列B中估计有空值
是什么字符型的 要改成varchar2