当列应成为唯一时合并相关数据
|
给定具有表的mysql数据库，如下所示：
author:
+----+----------+
| id | name |
+----+----------+
| 1 | John |
| 2 | Peter |
| 3 | Peter |
+----+----------+
article:
+----+-----------+------+
| id | author_id | text |
+----+-----------+------+
| 1 | 2 | ... |
| 2 | 3 | ... |
| 3 | 3 | ... |
+----+-----------+------+
没有找到相关结果
已邀请:
2 个回复
夏瓤跋棘
表格，这将达到目的：update article art set art.author_id = (select min(aut.id) from author aut where aut.name = (select a.name from author a where a.id = art.author_id)); select * from article; + ------- + -------------- + --------- + | id | author_id | text | + ------- + -------------- + --------- + | 1 | 2 | | | 2 | 2 | | | 3 | 2 | | + ------- + -------------- + --------- + 3 rowsupdate article art set art.author_id = (select min(aut.id) from author aut inner join author a on a.name = aut.name where a.id = art.author_id);delete a from author a inner join ( select name, min(id) as min -- this subquery returns all repeated names and their smallest id from author group by name having count(*) > 1) repeated on repeated.name = a.name where a.id > repeated.min; -- delete all repeateds except the first one select * from author; + ------- + --------- + | id | name | + ------- + --------- + | 1 | John | | 2 | Peter | + ------- + --------- + 2 rows舜辉
UPDATE art SET art.author_id = (SELECT MIN(a1.id) FROM author a1 WHERE a1.Name = a2.name FROM article art INNER JOIN author a2 ON art.author_id = a2.id)