Home
Linux
Golang
MySQL
PHP
Other
MySQL查找重复记录
创建日期:2019-12-18 11:42:23
更新日期:2023-02-06 18:00:29
栏目:
MySQL
浏览:540
# 准备表和数据 - 准备表 ``` CREATE TABLE `test_tmp` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `openid` VARCHAR(50) NOT NULL COMMENT 'openID', `unionid` VARCHAR(50) NULL DEFAULT NULL COMMENT 'unionID', `task_id` INT(11) NOT NULL COMMENT '领取的任务ID', `state` INT(11) NOT NULL DEFAULT '1' COMMENT '1进行中 2已完成 3失败', `created_at` TIMESTAMP NULL DEFAULT NULL, `updated_at` TIMESTAMP NULL DEFAULT NULL, PRIMARY KEY (`id`) ) COMMENT='临时测试表' ENGINE=InnoDB; ``` - 准备数据 ``` for ($i = 0; $i < 1000; $i++) { $data[] = [ 'openid' => 'user_' . mt_rand(1, 100), 'unionid' => md5('shuxiaoyuan' . mt_rand(1, 100)), 'task_id' => mt_rand(1, 10), 'state' => mt_rand(1, 3), 'created_at' => date('Y-m-d H:i:s'), 'updated_at' => date('Y-m-d H:i:s'), ]; } DB::table('test_tmp')->insert($data); ``` # 查询 - 查询出重复的字段和重复的数目,并按照重复的数量排序 ``` SELECT openid,COUNT(openid) AS a FROM test_tmp GROUP BY openid HAVING COUNT(*) > 1 ORDER BY a DESC; ``` 查询结果如下所示:  - 有多少人领取了重复的任务(就是 openID 和 task_id 两个字段重复)并计算重复的个数 ``` SELECT openid,task_id,COUNT(*) FROM test_tmp GROUP BY openid,task_id HAVING COUNT(*) > 1; ``` 查询结果如下所示:  可以进一步验证查询结果是否正确,经验证,正确无误
内容版权声明:本文为舒孝元原创文章,转载无需和我联系,但请注明来自
舒孝元博客:https://www.shuxiaoyuan.com/info/51
联系邮箱:sxy@shuxiaoyuan.com