我有一个包含以下列的用户表,我提出了一个查询,根据名字和姓氏来标识重复的记录。
表结构

CREATE TABLE IF NOT EXISTS `User` (
  `id` bigint(20) NOT NULL,
  `address1` text COLLATE utf8_unicode_ci,
  `mobile` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `firstName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lastName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `username` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `password` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `gender` tinyint(2) NOT NULL DEFAULT '0' COMMENT '1 - female, 2-male, 0 - unknown',
  `zip` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `countryCode` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL,
  `joinedDate` datetime DEFAULT NULL,
  `dateOfBirth` date DEFAULT NULL COMMENT 'User date of birth'
) ENGINE=MyISAM AUTO_INCREMENT=63561 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

还有我的问题
SELECT id, firstName, lastName, mobile, email, count(*) as dupCount
FROM `User` `t`
WHERE firstName is not null and lastName is not null
GROUP BY firstName,lastName HAVING count(*) > 1
ORDER BY t.joinedDate DESC

下面是我运行查询时的结果截图
php - MYSQL查询可根据名字和姓氏查找重复记录-LMLPHP
我的数据集在这里http://pastebin.com/Y5qsTcFc
对不起,如果粘贴箱不工作。试试这个https://drive.google.com/file/d/0B3gG6MG9uh7KdmRGT3d0NWg2aVk/view?usp=sharing
当有电子邮件地址为空的记录时,我们会遇到问题。我总是犯这个错误。
1062-密钥“groupúkey”的重复条目“Rachel Besnainou”
php - MYSQL查询可根据名字和姓氏查找重复记录-LMLPHP
我运行的查询是:
SELECT id, firstName, lastName, mobile, email, count(*) as dupCount
FROM `User` `t`
WHERE firstName is not null and lastName is not null
GROUP BY firstName,lastName
HAVING count(*) > 1

我不知道为什么会出现这个错误。感谢任何帮助
更新1
我试着把它添加到我的数据库中并运行查询,但仍然是相同的
SET SESSION max_heap_table_size=536870912;

SET SESSION tmp_table_size=536870912;

参考:Duplicate entry for key 'group_key'
更新2
我在diff mysql和phpmyadmin版本中尝试了相同的数据库,结果成功了。看起来我的mysql版本有个bug。我的mysql版本是5.6.26

最佳答案

试试这个:

select a1.id,
a1.FirstName,
a1.LastName,
a1.mobile,
a1.email
from user a1
inner join
(
  select FirstName, LastName, count(id)
  from User
  where firstname is not null
  and lastname is not null
  group by FirstName, LastName
  having count(id)>1
) u1
    on u1.FirstName = a1.Firstname
    and u1.Lastname = a1.lastname
order by a1.joineddate

10-05 21:06
查看更多