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>