两表比较的SQL 语句怎么写,请帮忙
两表比较的SQL 语句怎么写,请帮忙
有两张表，表1，表2，
表1 有一个字段a1，表内容如下：
A123efg
AB12SE
A14BDFG
A789CDDFDG
BC678D
ABCD456F
表2 有两个字段a2,a3。
123 8
12 8
14 9
789 10
131 11
-------------------
如果表1字段a1的记录值有数字等于表2字段a2记录，把它查出来出结果如下表：
SQL语句怎么写，可以实现?
想得到的结果：
---------------------
（1）表1，表2比较后相同的记录
A123efg 123 8
AB12SE 12 8
A14BDFG 14 9
A789CDDFDG 789 10
[解决办法]
CREATE CURSOR AB (A1 C(10))
INSERT INTO AB VALUES ("A123efg")
INSERT INTO AB VALUES ("AB12SE")
INSERT INTO AB VALUES ("A14BDFG")
INSERT INTO AB VALUES ("A789CDDFDG")
INSERT INTO AB VALUES ("BC678D")
INSERT INTO AB VALUES ("ABCD456F")
CREATE CURSOR ABC (A2 C(10),A3 C(10))
INSERT INTO ABC VALUES ("123","8")
INSERT INTO ABC VALUES ("12","8")
INSERT INTO ABC VALUES ("14","9")
INSERT INTO ABC VALUES ("789","10")
INSERT INTO ABC VALUES ("131","11")
SELECT AB.A1,ABC.A2,ABC.A3 FROM ABC,AB WHERE OCCURS(ALLTRIM(ABC.A2),AB.A1)>0 INTO CURSOR AAA GROUP BY A2
BROWSE
[解决办法]
-- vfp 代码Create Cursor t1 (a1 C(10))Insert into t1 (a1) values ('A123efg')Insert into t1 (a1) values ('AB12SE')Insert into t1 (a1) values ('A14BDFG')Insert into t1 (a1) values ('A789CDDFDG')Insert into t1 (a1) values ('BC678D')Insert into t1 (a1) values ('ABCD456F')* Insert into t1 (a1) values ('BC123DDDDDDDD')Create Cursor t2 (a2 N(10), a3 N(10))Insert into t2 (a2, a3) values (123,8)Insert into t2 (a2, a3) values (12,8)Insert into t2 (a2, a3) values (14,9)Insert into t2 (a2, a3) values (789,10)Insert into t2 (a2, a3) values (131,11)* Insert into t2 (a2, a3) values (12,999999)Select t1.a1, t2.a2, t2.a3 from t1 ;inner join t2 on Val(Chrtran(t1.a1, Chrtran(t1.a1,'0123456789',''), '')) == t2.a2