mysql heartbear_在SQL / MySQL中,当没有主ID索引时,可以使用左...

我想尝试使用外部联接找出表中的重复项：

如果表具有主索引ID,则以下外部联接可以找出重复的名称：

mysql> select * from gifts;

+--------+------------+-----------------+---------------------+

| giftID | name | filename | effectiveTime |

+--------+------------+-----------------+---------------------+

| 2 | teddy bear | bear.jpg | 2010-04-24 04:36:03 |

| 3 | coffee | coffee123.jpg | 2010-04-24 05:10:43 |

| 6 | beer | beer_glass.png | 2010-04-24 05:18:12 |

| 10 | heart | heart_shape.jpg | 2010-04-24 05:11:29 |

| 11 | ice tea | icetea.jpg | 2010-04-24 05:19:53 |

| 12 | cash | cash.png | 2010-04-24 05:27:44 |

| 13 | chocolate | choco.jpg | 2010-04-25 04:04:31 |

| 14 | coffee | latte.jpg | 2010-04-27 05:49:52 |

| 15 | coffee | espresso.jpg | 2010-04-27 06:03:03 |

+--------+------------+-----------------+---------------------+

9 rows in set (0.00 sec)

mysql> select * from gifts g1 LEFT JOIN (select * from gifts group by name) g2

on g1.giftID = g2.giftID where g2.giftID IS NULL;

+--------+--------+--------------+---------------------+--------+------+----------+---------------+

| giftID | name | filename | effectiveTime | giftID | name | filename | effectiveTime |

+--------+--------+--------------+---------------------+--------+------+----------+---------------+

| 14 | coffee | latte.jpg | 2010-04-27 05:49:52 | NULL | NULL | NULL | NULL |

| 15 | coffee | espresso.jpg | 2010-04-27 06:03:03 | NULL | NULL | NULL | NULL |

+--------+--------+--------------+---------------------+--------+------+----------+---------------+

2 rows in set (0.00 sec)

但是,如果表没有主索引ID,那么仍然可以使用外部联接来查找重复项吗？

附言也感谢任何非外部联接解决方案.如果可能,我想检查在没有主ID索引的情况下是否可以使用外部联接来完成此操作.感谢您的帮助.