我有一个包含以下列的用户表,我提出了一个查询,根据名字和姓氏来标识重复的记录。
表结构
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
下面是我运行查询时的结果截图
我的数据集在这里http://pastebin.com/Y5qsTcFc
对不起,如果粘贴箱不工作。试试这个https://drive.google.com/file/d/0B3gG6MG9uh7KdmRGT3d0NWg2aVk/view?usp=sharing
当有电子邮件地址为空的记录时,我们会遇到问题。我总是犯这个错误。
1062-密钥“groupúkey”的重复条目“Rachel Besnainou”
我运行的查询是:
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