我想取出MySQL数据库中的重复记录。这可以通过以下方式完成:
SELECT firstname,
lastname,
list.address
FROM list
INNER JOIN (SELECT address
FROM list
GROUP BY address
HAVING COUNT(id) > 1) dup
ON list.address = dup.address;这会导致:
JIM JONES 100 MAIN ST
JOHN SMITH 100 MAIN ST我想拉出它,这样它就会显示每一行都是重复的。类似于:
JIM JONES 100 MAIN ST如果结果为:
JIM JONES 100 MAIN ST
JOHN SMITH 100 MAIN ST
JOHN SMITH 100 MAIN ST我想拉出它,这样它就会显示每一行都是重复的。类似于:
JOHN SMITH 100 MAIN ST
JOHN SMITH 100 MAIN ST我只想要重复数据而不是原始数据
发布于 2019-03-18 11:42:40
您可以使用子查询来查找其相关重复记录的最小(Id)(在您的示例中,使用address与它们相关联)。这个min( id )将是您的原始id(我假设id是一个auto_increment PK)。因此,您可以使用id > this min(id)来查找所有多余的重复记录,请参见以下示例:
CREATE TABLE `person` (
`id` int not null auto_increment primary key,
`firstname` VARCHAR(32),
`lastname` VARCHAR(32),
`address` VARCHAR(128)
);
INSERT INTO `person`
(`firstname`, `lastname`, `address`)
VALUES
('Jim', 'Jones', '100 MAIN ST'),
('Jim', 'Smith', '100 MAIN ST'),
('John', 'Smith', '100 MAIN ST'),
('Alex', 'Smith', '23 Wall St'),
('Fox', 'Smith', '23 Wall St'),
('John', 'Smith', '100 main St');
SELECT id
, firstname
, lastname
, address
FROM person p
WHERE id > (
SELECT min(id) FROM person p1
WHERE p1.address = p.address
GROUP BY p1.address
HAVING count(*) > 1
)对于示例数据,此SQL将返回第2,3,5,6行,因为我们仅使用address检查重复项。您可以调整子查询以反映具有多个列的唯一约束,即地址+姓氏:
WHERE id > (
SELECT min(id) FROM person p1
WHERE p1.address = p.address
AND p1.lastname = p.lastname
GROUP BY p1.address, p1.lastname
HAVING count(*) > 1
)发布于 2019-03-18 12:44:48
尝试使用子查询
select * from person
where (firstname
, lastname
, address) in (
select firstname
, lastname
, address from person
group by firstname
, lastname
, address having count(*)>1
)输出
id firstname lastname address
3 John Smith 100 MAIN ST
6 John Smith 100 main Sthttps://stackoverflow.com/questions/55208148
复制相似问题