SQL query duplicate records)

Assume that there is a personnel table (table name: person). If you want to find records with the same names, ID card numbers, and addresses, use

   1:  SELECT p1.*   

   2:  FROM persons   p1,persons   p2   

   3:  WHERE p1.id<>p2.id   

   4:  AND p1.cardid   =   p2.cardid   

   5:  AND p1.pname   =   p2.pname   

   6:  AND p1.address   =   p2.address

This function can be implemented.

SQL statement used to delete duplicate records

1. Use the rowid Method

2. Use the group by Method

3. Use the distinct method

1. Rowid Method

The statement is as follows:

Query data:

Select * From Table1 A where rowid! = (Select max (rowid)
From Table1 B where a. name1 = B. name1 and A. name2 = B. name2 ......)

Delete data:

Delete from Table1 A where rowid! = (Select max (rowid)
From Table1 B where a. name1 = B. name1 and A. name2 = B. name2 ......)

2. Group by Method

Query data:

Select count (Num), max (name) from student -- lists the number of repeated records and its name attribute

Group by num

Having count (Num)> 1 -- group by num to find the num column in the table, that is, more than once

Delete data:

Delete from student

Group by num

Having count (Num)> 1

In this way, all duplicates are deleted.

3. Use the distinct method-Useful for small tables

Create Table table_new as select distinct * From Table1 minux

Truncate table Table1;

Insert into Table1 select * From table_new;

How to query and delete duplicate records

1. Search for redundant duplicate records in the Table. duplicate records are determined based on a single field (peopleid ).

Select * from people

Where peopleid in (select peopleid from people group by peopleid having count (peopleid)> 1)

2. Delete unnecessary duplicate records in the Table. Repeat records are determined based on a single field (eagleid), leaving only the records with the smallest rowid

Delete from people

Where peopleid in (select peopleid from people group by peopleid having count (peopleid)> 1)

And rowid not in (select Min (rowid) from people group by peopleid having count (peopleid)> 1)

3. Search for redundant duplicate records in the table (multiple fields)

Select * From vitae

Where (A. peopleid, A. seq) in (select peopleid, seq from vitae group by peopleid, seq having count (*)> 1)

4. Delete redundant record (multiple fields) in the table, leaving only the records with the smallest rowid

Delete from vitae

Where (A. peopleid, A. seq) in (select peopleid, seq from vitae group by peopleid, seq having count (*)> 1)

And rowid not in (select Min (rowid) from vitae group by peopleid, seq having count (*)> 1)

5. Search for redundant duplicate records (multiple fields) in the table, excluding records with the smallest rowid

Select * From vitae

Where (A. peopleid, A. seq) in (select peopleid, seq from vitae group by peopleid, seq having count (*)> 1)

And rowid not in (select Min (rowid) from vitae group by peopleid, seq having count (*)> 1)

(2) For example, there is a field "name" in table ",

The "name" value may be the same for different records,

Now, you need to query items with duplicate "name" values between records in the table;

Select name, count (*) from a group by name having count (*)> 1

If the gender is also the same, the statement is as follows:

Select name, sex, count (*) from a group by name, sex having count (*)> 1

(3) method 1

Declare @ Max integer, @ ID integer

Declare cur_rows cursor local for Select Main field, count (*) from table name group by main field having count (*)>; 1

Open cur_rows

Fetch cur_rows into @ ID, @ Max

While @ fetch_status = 0

Begin

Select @ max = @ max-1

Set rowcount @ Max

Delete from table name where primary field = @ ID

Fetch cur_rows into @ ID, @ Max

End

Close cur_rows

Set rowcount 0

Method 2

"Repeat record" has two duplicate records. One is a completely repeated record, that is, a record with all fields already exists. The other is a record with duplicate key fields, for example, the name field is repeated, while other fields are not necessarily repeated or can be ignored.

1. For the first type of repetition, it is easier to solve.

Select distinct * From tablename

You can get the result set without repeated records.

If the table needs to delete duplicate records (one record is retained), you can delete the record as follows:

Select distinct * into # TMP from tablename

Drop table tablename

Select * into tablename from # TMP

Drop table # TMP

The reason for this repetition is that the table design is not weekly. You can add a unique index column.

2. Repeat problems usually require that the first record in the repeat record be retained. The procedure is as follows:

Assume that the duplicate fields are Name and address. You must obtain the unique result set of the two fields.

Select Identity (INT, 1, 1) as autoid, * into # TMP from tablename

Select min (autoid) as autoid into # tmp2 from # TMP group by name, autoid

Select * from # TMP where autoid in (select autoid from # tmp2)

The last SELECT command gets the result set with no duplicate name and address (but an autoid field is added, which can be omitted in the select clause when writing)

(4) Duplicate queries

Select * From tablename where ID in (

Select ID from tablename

Group by ID

Having count (ID)> 1)

Http://www.cnblogs.com/caotang/archive/2011/01/18/1937932.html

Assume that there is a personnel table (table name: person). If you want to find records with the same names, ID card numbers, and addresses, use

   1:  SELECT p1.*   

   2:  FROM persons   p1,persons   p2   

   3:  WHERE p1.id<>p2.id   

   4:  AND p1.cardid   =   p2.cardid   

   5:  AND p1.pname   =   p2.pname   

   6:  AND p1.address   =   p2.address

This function can be implemented.

SQL statement used to delete duplicate records

1. Use the rowid Method

2. Use the group by Method

3. Use the distinct method

1. Rowid Method

The statement is as follows:

Query data:

Select * From Table1 A where rowid! = (Select max (rowid)
From Table1 B where a. name1 = B. name1 and A. name2 = B. name2 ......)

Delete data:

Delete from Table1 A where rowid! = (Select max (rowid)
From Table1 B where a. name1 = B. name1 and A. name2 = B. name2 ......)

2. Group by Method

Query data:

Select count (Num), max (name) from student -- lists the number of repeated records and its name attribute

Group by num

Having count (Num)> 1 -- group by num to find the num column in the table, that is, more than once

Delete data:

Delete from student

Group by num

Having count (Num)> 1

In this way, all duplicates are deleted.

3. Use the distinct method-Useful for small tables

Create Table table_new as select distinct * From Table1 minux

Truncate table Table1;

Insert into Table1 select * From table_new;

How to query and delete duplicate records

1. Search for redundant duplicate records in the Table. duplicate records are determined based on a single field (peopleid ).

Select * from people

Where peopleid in (select peopleid from people group by peopleid having count (peopleid)> 1)

2. Delete unnecessary duplicate records in the Table. Repeat records are determined based on a single field (eagleid), leaving only the records with the smallest rowid

Delete from people

Where peopleid in (select peopleid from people group by peopleid having count (peopleid)> 1)

And rowid not in (select Min (rowid) from people group by peopleid having count (peopleid)> 1)

3. Search for redundant duplicate records in the table (multiple fields)

Select * From vitae

Where (A. peopleid, A. seq) in (select peopleid, seq from vitae group by peopleid, seq having count (*)> 1)

4. Delete redundant record (multiple fields) in the table, leaving only the records with the smallest rowid

Delete from vitae

Where (A. peopleid, A. seq) in (select peopleid, seq from vitae group by peopleid, seq having count (*)> 1)

And rowid not in (select Min (rowid) from vitae group by peopleid, seq having count (*)> 1)

5. Search for redundant duplicate records (multiple fields) in the table, excluding records with the smallest rowid

Select * From vitae

Where (A. peopleid, A. seq) in (select peopleid, seq from vitae group by peopleid, seq having count (*)> 1)

And rowid not in (select Min (rowid) from vitae group by peopleid, seq having count (*)> 1)

(2) For example, there is a field "name" in table ",

The "name" value may be the same for different records,

Now, you need to query items with duplicate "name" values between records in the table;

Select name, count (*) from a group by name having count (*)> 1

If the gender is also the same, the statement is as follows:

Select name, sex, count (*) from a group by name, sex having count (*)> 1

(3) method 1

Declare @ Max integer, @ ID integer

Declare cur_rows cursor local for Select Main field, count (*) from table name group by main field having count (*)>; 1

Open cur_rows

Fetch cur_rows into @ ID, @ Max

While @ fetch_status = 0

Begin

Select @ max = @ max-1

Set rowcount @ Max

Delete from table name where primary field = @ ID

Fetch cur_rows into @ ID, @ Max

End

Close cur_rows

Set rowcount 0

Method 2

"Repeat record" has two duplicate records. One is a completely repeated record, that is, a record with all fields already exists. The other is a record with duplicate key fields, for example, the name field is repeated, while other fields are not necessarily repeated or can be ignored.

1. For the first type of repetition, it is easier to solve.

Select distinct * From tablename

You can get the result set without repeated records.

If the table needs to delete duplicate records (one record is retained), you can delete the record as follows:

Select distinct * into # TMP from tablename

Drop table tablename

Select * into tablename from # TMP

Drop table # TMP

The reason for this repetition is that the table design is not weekly. You can add a unique index column.

2. Repeat problems usually require that the first record in the repeat record be retained. The procedure is as follows:

Assume that the duplicate fields are Name and address. You must obtain the unique result set of the two fields.

Select Identity (INT, 1, 1) as autoid, * into # TMP from tablename

Select min (autoid) as autoid into # tmp2 from # TMP group by name, autoid

Select * from # TMP where autoid in (select autoid from # tmp2)

The last SELECT command gets the result set with no duplicate name and address (but an autoid field is added, which can be omitted in the select clause when writing)

(4) Duplicate queries

Select * From tablename where ID in (

Select ID from tablename

Group by ID

Having count (ID)> 1)

Http://www.cnblogs.com/caotang/archive/2011/01/18/1937932.html