代码之家 › 专栏 › 技术社区 › nonopolarity

在sql/mysql中,当没有主id索引时,是否可以使用左外连接来查找重复项?

outer-join duplicates mysql sql

nonopolarity · 技术社区 · 14 年前

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

如果表具有主索引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,那么外部连接仍然可以用来查找重复项呢?

附笔. 感谢您提供任何非外部连接解决方案。如果可能的话,我想检查在没有主i d索引的情况下是否可以使用外部连接来完成。谢谢你的帮助。

2 回复 | 直到 14 年前

Quassnoi 14 年前

使用 EXISTS 条款:

SELECT  *
FROM    gifts go
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    gifts gi
        WHERE   gi.name = go.name
        LIMIT 1, 1
        )

Guffa 14 年前

可以使用分组查找重复值:

select name
from gifts
group by name
having count(*) > 1