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