MySQL去掉重复记录

mysql> select * from test;
+--------+------+-----------------+---------+-------------------------------+
| p_id   | g_id | p_name          | p_price | p_desc                        |
+--------+------+-----------------+---------+-------------------------------+
| Beef1 | 1003 | Beef （1 stick） |    2.50 | Beef, pink, single stick      |
| Beef2 | 1003 | Beef （5 sticks） |   11.00 | Beef, pink, pack of 10 sticks |
| CAB    | 1003 | Cable           |    4.42 | Cable, one size fits all      |
| FB     | 1003 | Seed            |   11.50 | L bag                         |
| FU1    | 1002 | Fuses           |    3.42 | 1 dozen, extra long           |
| IRON01 | 1001 | .5 kg iron      |    4.99 | .5 kg iron, black             |
| IRON02 | 1001 | 1 kg iron       |    8.99 | 1 kg iron, black              |
| IRON03 | 1001 | 2 kg iron       |   13.99 | 2 kg iron, black              |
| JP1100 | 1005 | Jet 1000        |   36.00 | Jet 1000, single use          |
| JP2000 | 1005 | Jet 2000        |   51.00 | Jet 2000, multi-use           |
| OIL1   | 1002 | Oil bottle      |    7.99 | Oil bottal, red               |
| PO     | 1003 | Potatoes        |    2.50 | Potatoes                      |
| SB     | 1003 | Safebox         |   51.00 | Safe with combination lock    |
| TP     | 1003 | Toothpaste      |   12.00 | Toothpaste, 10 pack           |
| Beef1 | 1003 | Beef （1 stick） |    2.50 | Beef, pink, single stick      |
| Beef2 | 1003 | Beef （5 sticks） |   11.00 | Beef, pink, pack of 10 sticks |
| CAB    | 1003 | Cable           |    4.42 | Cable, one size fits all      |
| FB     | 1003 | Seed            |   11.50 | L bag                         |
| FU1    | 1002 | Fuses           |    3.42 | 1 dozen, extra long           |
| IRON01 | 1001 | .5 kg iron      |    4.99 | .5 kg iron, black             |
| IRON02 | 1001 | 1 kg iron       |    8.99 | 1 kg iron, black              |
| IRON03 | 1001 | 2 kg iron       |   13.99 | 2 kg iron, black              |
| JP1100 | 1005 | Jet 1000        |   36.00 | Jet 1000, single use          |
| JP2000 | 1005 | Jet 2000        |   51.00 | Jet 2000, multi-use           |
| OIL1   | 1002 | Oil bottle      |    7.99 | Oil bottal, red               |
| PO     | 1003 | Potatoes        |    2.50 | Potatoes                      |
| SB     | 1003 | Safebox         |   51.00 | Safe with combination lock    |
| TP     | 1003 | Toothpaste      |   12.00 | Toothpaste, 10 pack           |
| Beef1 | 1003 | Beef （1 stick） |    2.50 | Beef, pink, single stick      |
| Beef2 | 1003 | Beef （5 sticks） |   11.00 | Beef, pink, pack of 10 sticks |
| CAB    | 1003 | Cable           |    4.42 | Cable, one size fits all      |
| FB     | 1003 | Seed            |   11.50 | L bag                         |
| FU1    | 1002 | Fuses           |    3.42 | 1 dozen, extra long           |
| IRON01 | 1001 | .5 kg iron      |    4.99 | .5 kg iron, black             |
| IRON02 | 1001 | 1 kg iron       |    8.99 | 1 kg iron, black              |
| IRON03 | 1001 | 2 kg iron       |   13.99 | 2 kg iron, black              |
| JP1100 | 1005 | Jet 1000        |   36.00 | Jet 1000, single use          |
| JP2000 | 1005 | Jet 2000        |   51.00 | Jet 2000, multi-use           |
| OIL1   | 1002 | Oil bottle      |    7.99 | Oil bottal, red               |
| PO     | 1003 | Potatoes        |    2.50 | Potatoes                      |
| SB     | 1003 | Safebox         |   51.00 | Safe with combination lock    |
| TP     | 1003 | Toothpaste      |   12.00 | Toothpaste, 10 pack           |
+--------+------+-----------------+---------+-------------------------------+
42 rows in set （0.00 sec）mysql> create table tmp as （select * from test group by p_id having count（*）=1）; //创建临时表，复制表结构
Query OK, 0 rows affected （0.03 sec）
Records: 0 Duplicates: 0 Warnings: 0mysql> insert into tmp（select * from test group by p_id having count（*）>1）; //复制数据
Query OK, 14 rows affected （0.00 sec）
Records: 14 Duplicates: 0 Warnings: 0mysql> truncate table test;
Query OK, 0 rows affected （0.00 sec）mysql> insert into test select * from tmp;
Query OK, 14 rows affected （0.00 sec）
Records: 14 Duplicates: 0 Warnings: 0mysql> select * from tmp;
+--------+------+-----------------+---------+-------------------------------+
| p_id   | g_id | p_name          | p_price | p_desc                        |
+--------+------+-----------------+---------+-------------------------------+
| Beef1 | 1003 | Beef （1 stick） |    2.50 | Beef, pink, single stick      |
| Beef2 | 1003 | Beef （5 sticks） |   11.00 | Beef, pink, pack of 10 sticks |
| CAB    | 1003 | Cable           |    4.42 | Cable, one size fits all      |
| FB     | 1003 | Seed            |   11.50 | L bag                         |
| FU1    | 1002 | Fuses           |    3.42 | 1 dozen, extra long           |
| IRON01 | 1001 | .5 kg iron      |    4.99 | .5 kg iron, black             |
| IRON02 | 1001 | 1 kg iron       |    8.99 | 1 kg iron, black              |
| IRON03 | 1001 | 2 kg iron       |   13.99 | 2 kg iron, black              |
| JP1100 | 1005 | Jet 1000        |   36.00 | Jet 1000, single use          |
| JP2000 | 1005 | Jet 2000        |   51.00 | Jet 2000, multi-use           |
| OIL1   | 1002 | Oil bottle      |    7.99 | Oil bottal, red               |
| PO     | 1003 | Potatoes        |    2.50 | Potatoes                      |
| SB     | 1003 | Safebox         |   51.00 | Safe with combination lock    |
| TP     | 1003 | Toothpaste      |   12.00 | Toothpaste, 10 pack           |
+--------+------+-----------------+---------+-------------------------------+
14 rows in set （0.00 sec）mysql> MySQL中快速复制数据表方法汇总Oracle 批量绑定forall bulk collect用法相关资讯 mysql

数据库服务器 MySQL （08/15/2013 06:50:23）
MySQL 5.6 GA 及逃亡潮（02/08/2013 14:36:35）
MySQL 5.5.22、5.1.62、5.0.96全线（03/22/2012 19:03:49）

MySQL Administrator连接VMWare下（05/24/2013 09:20:58）
MySQL 5.1.68 发布（02/05/2013 08:37:47）
CentOS 5.2+MySQL+Heartbeat双机互（01/29/2012 11:16:55）

本文评论查看全部评论（0）