MySQL查找重复记录

# 准备表和数据

- 准备表

```
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;
```
查询结果如下所示：
![](https://shuxiaoyuan-201812.oss-cn-shanghai.aliyuncs.com/sxy_book/20191203/20191203161527.png)

- 有多少人领取了重复的任务（就是 openID 和 task_id 两个字段重复）并计算重复的个数

```
SELECT openid,task_id,COUNT(*) FROM test_tmp GROUP BY openid,task_id HAVING COUNT(*) > 1;
```
查询结果如下所示：
![图三](https://shuxiaoyuan-201812.oss-cn-shanghai.aliyuncs.com/sxy_book/20191203/20191203133938.png "图三")

可以进一步验证查询结果是否正确，经验证，正确无误