1. During the interview, I encountered a problem. I wrote a table with two fields, ID and name, to query all the data with duplicate names. The column is:
Select * from Xi A where (A. username) in (select username from Xi Group by username having count (*)> 1)
2. After all the data is grouped and repeated data is queried, the following columns are listed first:
Select count (username) as 'repeat', username from Xi Group by username having count (*)> 1 order by username DESC
3. To view others' results, the following column is displayed: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 sex 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 integerdeclare cur_rows cursor local for Select Main field, count (*) from table name group by main field having count (*)>; 1 open cur_rowsfetch cur_rows into @ ID, @ maxwhile @ fetch_status = 0 beginselect @ max = @ max-1 Set rowcount @ maxdelete from table name where
Main field = @ idfetch cur_rows into @ ID, @ maxendclose cur_rowsset 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 repeated 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 repeat, it is easy to solve. You can use select distinct * From tablename to obtain the result set without repeated records. If the table needs to delete duplicate records (one record is retained ), you can delete select distinct * into # TMP from tablenameddrop table tablenameselect * into tablename from # tmpdrop table # TMP. This duplication occurs because the table is not designed for weeks, you can add a unique index column.
2. This type of repetition problem usually requires that the first record in the repeat record be retained. The operation method is as follows, assuming that there are repeated fields: name, address, select Identity (INT, 1, 1) as autoid, * into # TMP from tablenameselect min (autoid) as autoid into # tmp2 from # TMP group by name, autoidselect * from # TMP where autoid in (select autoid from
# Tmp2) The last select gets the result set with name and address not repeated (but an autoid field is added, which can be omitted in the select clause in actual writing)
(4)
Query repeated select * From tablename where ID in (select ID from tablenamegroup by idhaving count (ID)> 1)
1. During the interview, I encountered a problem. I wrote a table with two fields, ID and name, to query all the data with duplicate names. The column is:
Select * from Xi A where (A. username) in (select username from Xi Group by username having count (*)> 1)
2. After all the data is grouped and repeated data is queried, the following columns are listed first:
Select count (username) as 'repeat', username from Xi Group by username having count (*)> 1 order by username DESC
3. To view others' results, the following column is displayed: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 sex 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 integerdeclare cur_rows cursor local for Select Main field, count (*) from table name group by main field having count (*)>; 1 open cur_rowsfetch cur_rows into @ ID, @ maxwhile @ fetch_status = 0 beginselect @ max = @ max-1 Set rowcount @ maxdelete from table name where
Main field = @ idfetch cur_rows into @ ID, @ maxendclose cur_rowsset 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 repeated 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 repeat, it is easy to solve. You can use select distinct * From tablename to obtain the result set without repeated records. If the table needs to delete duplicate records (one record is retained ), you can delete select distinct * into # TMP from tablenameddrop table tablenameselect * into tablename from # tmpdrop table # TMP. This duplication occurs because the table is not designed for weeks, you can add a unique index column.
2. This type of repetition problem usually requires that the first record in the repeat record be retained. The operation method is as follows, assuming that there are repeated fields: name, address, select Identity (INT, 1, 1) as autoid, * into # TMP from tablenameselect min (autoid) as autoid into # tmp2 from # TMP group by name, autoidselect * from # TMP where autoid in (select autoid from
# Tmp2) The last select gets the result set with name and address not repeated (but an autoid field is added, which can be omitted in the select clause in actual writing)
(4)
Query repeated select * From tablename where ID in (select ID from tablenamegroup by idhaving count (ID)> 1)