MySQL data duplicate checking and deduplication implementation statements
|
There is a table user, and the fields are id, nick_name, password, email, and phone. 1. Single field (nick_name) Find all records with duplicate records select * from user where nick_name in (select nick_name from user group by nick_name having count(nick_name)>1); Find the record with the largest ID in each record group with duplicate records select * from user where id in (select max(id) from user group by nick_name having count(nick_name)>1); Find out the redundant records, but not the record with the smallest id select * from user where nick_name in (select nick_name from user group by nick_name having count(nick_name)>1) and id not in (select min(id) from user group by nick_name having count(nick_name)>1); Delete redundant duplicate records and keep only the record with the smallest id delete from user where nick_name in (select nick_name from (select nick_name from user group by nick_name having count(nick_name)>1) as tmp1) and id not in (select id from (select min(id) from user group by nick_name having count(nick_name)>1) as tmp2); 2. Multiple fields (nick_name, password) Find all the records with duplicate records select * from user where (nick_name,password) in (select nick_name,password from user group by nick_name,password where having count(nick_name)>1); Find the record with the largest ID in each record group with duplicate records select * from user where id in (select max(id) from user group by nick_name,password where having count(nick_name)>1); Find the redundant records in each duplicate record group, but do not find the one with the smallest ID. select * from user where (nick_name,password) in (select nick_name,password from user group by nick_name,password having count(nick_name)>1) and id not in (select min(id) from user group by nick_name,password having count(nick_name)>1); Delete redundant duplicate records and keep only the record with the smallest id delete from user where (nick_name,password) in (select nick_name,password from (select nick_name,password from user group by nick_name,password having count(nick_name)>1) as tmp1) and id not in (select id from (select min(id) id from user group by nick_name,password having count(nick_name)>1) as tmp2); The above is the detailed content of the implementation statements for MySQL data duplication checking and deduplication. For more information about MySQL data duplication checking and deduplication, please pay attention to other related articles on 123WORDPRESS.COM! You may also be interested in:
|
<<: A brief analysis of Linux resolv.conf
>>: Various correct postures for using environment variables in Webpack
Recommend
How to check if a table exists in MySQL and then delete it in batches
1. I searched for a long time on the Internet but...
A new CSS image replacement technique (background display and text moving off screen) to say goodbye to 9999px
-9999 px image replacement technology has been pop...
How to use vue-video-player to achieve live broadcast
Table of contents 1. Install vue-video-player 2. ...
HTML background color gradient achieved through CSS
Effect screenshots: Implementation code: Copy code...
How to use negative margin technology to achieve average layout in CSS
We usually use float layout to solve the compatib...
MySQL Basics in 1 Hour
Table of contents Getting Started with MySQL MySQ...
Example of deploying Laravel application with Docker
The PHP base image used in this article is: php:7...
What are the drawbacks of deploying the database in a Docker container?
Preface Docker has been very popular in the past ...
The difference between HTML name id and class_PowerNode Java Academy
name Specify a name for the tag. Format <input...
VMware Workstation Pro installs Win10 pure version operating system
This article describes the steps to install the p...
MySQL: mysql functions
1. Built-in functions 1. Mathematical functions r...
Detailed explanation of Nginx installation, SSL configuration and common commands under Centos7.x
1. Installation Install using yum ##Automatically...
Detailed tutorial on how to connect to a remote server Docker to deploy a Spring Boot project in IDEA
Before you begin Have a cloud server, mine is Ten...
JS Difficulties Synchronous and Asynchronous and Scope and Closure and Detailed Explanation of Prototype and Prototype Chain
Table of contents JS Three Mountains Synchronous ...
9 Tips for MySQL Database Optimization
Table of contents 1. Choose the most appropriate ...