mysql> CREATE TABLE b (SELECT `name`, max(startdate), enddate FROM A GROUP By name, enddate); Query OK, 2 rows affected (0.06 sec) Records: 2 Duplicates: 0 Warnings: 0mysql> drop table a; Query OK, 0 rows affected (0.00 sec)mysql> rename table b to a; Query OK, 0 rows affected (0.02 sec)mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | a | +----------------+ 1 row in set (0.00 sec)mysql> select * from a; +-------+---------------------+---------------------+ | name | max(startdate) | enddate | +-------+---------------------+---------------------+ | name1 | 2004-09-26 00:00:00 | 2004-09-30 00:00:00 | | name2 | 2004-09-26 00:00:00 | 2005-01-03 00:00:00 | +-------+---------------------+---------------------+ 2 rows in set (0.00 sec)我也是新手。应该有比这个简单的方法。
这个做法有一个问题,会丢失原表里的所有的索引,而且某些字段的类型也会发生变化。比如 AUTO_INCREMENT 将不在保留,VARCHAR 可能变成 CHAR 。所以,是不是 应该先 CREATE TABLE B LIKE A, 然后再INSERT INTO B (SELECT `name`, max(startdate), enddate FROM A GROUP By name, enddate), 以后再 DROP A, 最后再 RENAME B TO A ?不确定这样有没有用。 顺便补充,上面的操作都在 MySQL 4.1 以上运行的。
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> drop table a;
Query OK, 0 rows affected (0.00 sec)mysql> rename table b to a;
Query OK, 0 rows affected (0.02 sec)mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| a |
+----------------+
1 row in set (0.00 sec)mysql> select * from a;
+-------+---------------------+---------------------+
| name | max(startdate) | enddate |
+-------+---------------------+---------------------+
| name1 | 2004-09-26 00:00:00 | 2004-09-30 00:00:00 |
| name2 | 2004-09-26 00:00:00 | 2005-01-03 00:00:00 |
+-------+---------------------+---------------------+
2 rows in set (0.00 sec)我也是新手。应该有比这个简单的方法。
应该先 CREATE TABLE B LIKE A,
然后再INSERT INTO B (SELECT `name`, max(startdate), enddate FROM A GROUP By name, enddate),
以后再 DROP A,
最后再 RENAME B TO A ?不确定这样有没有用。
顺便补充,上面的操作都在 MySQL 4.1 以上运行的。
有没有可以用一句Mysql的sql语句搞定的方法呢?