这向我显示了所有具有完全相同的两个条目的名字和姓氏
SELECT `firstname`,`lastname`,COUNT(*) AS Count
FROM `people`
GROUP BY `firstname`,`lastname`
HAVING Count = 2
如何将其转换为带有 LIMIT 的 DELETE FROM WHERE 语句,以仅删除每个条目之一并保留另一个条目。
好吧,这似乎是技术性的,我只是要在 php while 循环中完成它
您可以创建一个表,其中每个重复项都有 1 条记录:然后从 people 表中删除所有重复记录,然后重新插入重复记录。
-- Setup for example
create table people (fname varchar(10), lname varchar(10));
insert into people values ('Bob', 'Newhart');
insert into people values ('Bob', 'Newhart');
insert into people values ('Bill', 'Cosby');
insert into people values ('Jim', 'Gaffigan');
insert into people values ('Jim', 'Gaffigan');
insert into people values ('Adam', 'Sandler');
-- Show table with duplicates
select * from people;
-- Create table with one version of each duplicate record
create table dups as
select distinct fname, lname, count(*)
from people group by fname, lname
having count(*) > 1;
-- Delete all matching duplicate records
delete people from people inner join dups
on people.fname = dups.fname AND
people.lname = dups.lname;
-- Insert single record of each dup back into table
insert into people select fname, lname from dups;
-- Show Fixed table
select * from people;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)