MySQL去掉重复记录

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: 0

mysql> 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: 0

mysql> 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: 0

mysql> 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>