数据库版本 MySQL 5.1.41
有2个表,现要将表一的id和sid字段插入到表二(对应tid和sid)
表一的pid和color字段如果重复的情况下,则插入的tid为最小的那条纪录的id(sid不变)
表的结构如下CREATE TABLE `t1`(
`id` int(1) unsigned NOT NULLL AUTO_INCREMENT,
`pid` mediumint(1) unsigned NOT NULL,
`sid` int(1) unsigned NOT NULL,
`color` char(20) CHARCTER SET gbk NOT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `t2`(
`tid` int(1) unsigned NOT NULL,
`sid` int(1) unsigned NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
测试数据
INSERT INTO `t1`(`id`,`pid`,`sid`,`color`) VALUES
(1,1,1,'abc'),
(2,1,1,'ijk'),
(3,1,1,'xyz'),
(4,1,2,'abc'),/*与id=1重复*/
(5,1,2,'qwer'),
(6,1,3,'qwer'),/*与id=5重复*/
(7,1,3,'ijk'),/*与id=2重复*/
(8,2,4,'abc'),
(9,2,4,'xyz'),
(10,2,5,'abc'),/*与id=8重复*/
(11,2,5,'ijk'),
(12,2,5,'def'),
(13,2,5,'iop'),
(14,2,6,'asd'),
(15,2,6,'fgh');
期望结果
SELECT * FROM `t2`;
+-----+-----+
| tid | sid |
+-----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 1 | 2 |
| 5 | 2 |
| 5 | 3 |
| 2 | 3 |
| 8 | 4 |
| 9 | 4 |
| 8 | 5 |
| 11 | 5 |
| 12 | 5 |
| 13 | 5 |
| 14 | 6 |
| 15 | 6 |
+-----+-----+
有2个表,现要将表一的id和sid字段插入到表二(对应tid和sid)
表一的pid和color字段如果重复的情况下,则插入的tid为最小的那条纪录的id(sid不变)
表的结构如下CREATE TABLE `t1`(
`id` int(1) unsigned NOT NULLL AUTO_INCREMENT,
`pid` mediumint(1) unsigned NOT NULL,
`sid` int(1) unsigned NOT NULL,
`color` char(20) CHARCTER SET gbk NOT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `t2`(
`tid` int(1) unsigned NOT NULL,
`sid` int(1) unsigned NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
测试数据
INSERT INTO `t1`(`id`,`pid`,`sid`,`color`) VALUES
(1,1,1,'abc'),
(2,1,1,'ijk'),
(3,1,1,'xyz'),
(4,1,2,'abc'),/*与id=1重复*/
(5,1,2,'qwer'),
(6,1,3,'qwer'),/*与id=5重复*/
(7,1,3,'ijk'),/*与id=2重复*/
(8,2,4,'abc'),
(9,2,4,'xyz'),
(10,2,5,'abc'),/*与id=8重复*/
(11,2,5,'ijk'),
(12,2,5,'def'),
(13,2,5,'iop'),
(14,2,6,'asd'),
(15,2,6,'fgh');
期望结果
SELECT * FROM `t2`;
+-----+-----+
| tid | sid |
+-----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 1 | 2 |
| 5 | 2 |
| 5 | 3 |
| 2 | 3 |
| 8 | 4 |
| 9 | 4 |
| 8 | 5 |
| 11 | 5 |
| 12 | 5 |
| 13 | 5 |
| 14 | 6 |
| 15 | 6 |
+-----+-----+
解决方案 »
- 左连接问题
- 求一个MYSQL查询语句!
- 我的mysql数据库无法登陆
- mysql.com被黑出售其root权限
- 为何我用 mysqld 启动 mysql 的服务后,在系统“服务”中查不到mysql服务呢?
- mysql库多会不会有问题
- ++++++++请问一个sql查询语句?
- 高手::对于MYSQL数据库我一点也不熟,只会用SQL 2000, 现有一个项目需要用ASP+MYSQL,需求请教高手,必有重谢!
- 哪些webserver支持mysql?哪些支持oracle?哪些支持sybase?
- 【求助】MySQL5.7.10安装完成后导入以前的数据库会出现无法编辑数据表的情况呢?
- MySQL NDB CLuster中Undo Log和Redo Log的问题
- mysql5.1现在编码想转utf8。该如何转过来?
[征集]分组取最大N条记录方法征集,及散分....
-> `id` int(1) unsigned NOT NULLL AUTO_INCREMENT,
-> `pid` mediumint(1) unsigned NOT NULL,
-> `sid` int(1) unsigned NOT NULL,
-> `color` char(20) CHARCTER SET gbk NOT NULL,
-> PRIMARY KEY (`id`)
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'NULLL
AUTO_INCREMENT,
`pid` mediumint(1) unsigned NOT NULL,
`sid` int(1) uns' at line 2
mysql>
+----+-----+-----+-------+
| id | pid | sid | color |
+----+-----+-----+-------+
| 1 | 1 | 1 | abc |
| 2 | 1 | 1 | ijk |
| 3 | 1 | 1 | xyz |
| 4 | 1 | 2 | abc |
| 5 | 1 | 2 | qwer |
| 6 | 1 | 3 | qwer |
| 7 | 1 | 3 | ijk |
| 8 | 2 | 4 | abc |
| 9 | 2 | 4 | xyz |
| 10 | 2 | 5 | abc |
| 11 | 2 | 5 | ijk |
| 12 | 2 | 5 | def |
| 13 | 2 | 5 | iop |
| 14 | 2 | 6 | asd |
| 15 | 2 | 6 | fgh |
+----+-----+-----+-------+
15 rows in set (0.03 sec)mysql> insert into t2
-> select (Select min(id) from t1 where pid=a.pid and color=a.color),sid From t1 a;
Query OK, 15 rows affected (0.01 sec)
Records: 15 Duplicates: 0 Warnings: 0mysql> select * from t2;
+-----+-----+
| tid | sid |
+-----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 1 | 2 |
| 5 | 2 |
| 5 | 3 |
| 2 | 3 |
| 8 | 4 |
| 9 | 4 |
| 8 | 5 |
| 11 | 5 |
| 12 | 5 |
| 13 | 5 |
| 14 | 6 |
| 15 | 6 |
+-----+-----+
15 rows in set (0.00 sec)mysql>