DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,person_id INT NOT NULL
,address_id INT NOT NULL
);

INSERT INTO my_table VALUES
(1    ,   80       ,     3),
(2     ,  226  ,         3),
(3     ,  31   ,         5),
(4     ,  46   ,         9),
(5     ,  11   ,         9),
(6     ,  123  ,         9);

CREATE TABLE my_new_table AS
SELECT x.* FROM my_table x JOIN (SELECT MIN(id) id FROM my_table GROUP BY address_id) y ON y.id = x.id;

SELECT * FROM my_new_table;
+----+-----------+------------+
| id | person_id | address_id |
+----+-----------+------------+
|  1 |        80 |          3 |
|  3 |        31 |          5 |
|  4 |        46 |          9 |
+----+-----------+------------+
3 rows in set (0.01 sec)

ALTER TABLE my_new_table MODIFY id SERIAL PRIMARY KEY;

ALTER TABLE my_new_table MODIFY address_id INT UNIQUE;

您的选择似乎也不正确。你可以试试这个-

delete from person_address
 where id not in (select *
                    from (SELECT min(id)
                            FROM person_address
                           WHERE person_address.person_id < 400000
                           GROUP BY address_id) temp)
   and person_id < 400000

您可以通过在同一个表中创建两个对象来删除重复记录。喜欢

Delete p1 
from person_address p1, person_address p2
where
p1.person_id < 400000
and p1.address_id = p2.address_id
and p1.id > p2.id;