|
SET NAMES gb2312;
CREATE TABLE `o` (
`id` int(11) default NULL,
`name` varchar(11) default NULL
) TYPE=MyISAM;
insert into `o` values ('11','数据1');
insert into `o` values ('22','数据2');
insert into `o` values ('33','数据3');
insert into `o` values ('44','数据4');
CREATE TABLE `class1` (
`id` int(11) default NULL,
`name` varchar(11) default NULL
) TYPE=MyISAM;
insert into `class1` values ('11','数据3');
insert into `class1` values ('22','数据2');
insert into `class1` values ('33','数据4');
insert into `class1` values ('44','数据1');
CREATE TABLE `class2` (
`id` int(11) default NULL,
`name` varchar(50) default NULL
) TYPE=MyISAM;
insert into `class2` values ('11842542','子数据3');
insert into `class2` values ('22543253','子数据2');
insert into `class2` values ('33245235','子数据4');
insert into `class2` values ('44543254','子数据1');
SELECT *
, CONCAT(o.id, RIGHT(B.id, LENGTH(CAST( B.id AS CHAR)) - 2)) value
FROM class1 A, class2 B, o
where A.id = Left(B.id, 2)
and A.name = o.name;
+------+-------+----------+---------+------+-------+----------+
| id | name | id | name | id | name | value |
+------+-------+----------+---------+------+-------+----------+
| 44 | 数据1 | 44543254 | 子数据1 | 11 | 数据1 | 11543254 |
| 22 | 数据2 | 22543253 | 子数据2 | 22 | 数据2 | 22543253 |
| 11 | 数据3 | 11842542 | 子数据3 | 33 | 数据3 | 33842542 |
| 33 | 数据4 | 33245235 | 子数据4 | 44 | 数据4 | 44245235 |
+------+-------+----------+---------+------+-------+----------+
4 rows in set (0.00 sec)
UPDATE
class2
LEFT JOIN class1 ON class1.id = LEFT(class2.id, 2)
LEFT JOIN o ON class1.name = o.name
SET class2.ID = CONCAT(o.id, RIGHT(class2.id, LENGTH(CAST(class2.id AS CHAR)) - 2)) ;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 4 Changed: 3 Warnings: 0
SELECT * FROM class2;
+----------+---------+
| id | name |
+----------+---------+
| 33842542 | 子数据3 |
| 22543253 | 子数据2 |
| 44245235 | 子数据4 |
| 11543254 | 子数据1 |
+----------+---------+
4 rows in set (0.00 sec)
DROP TABLE `o`;
DROP TABLE `class1`;
DROP TABLE `class2`;
|