Shulou(Shulou.com)06/01 Report--

Editor to share with you how to re-query sql, I hope you have learned a lot after reading this article, let's discuss it together!

Sql to re-query method: duplicate records based on a single field peopleId to judge, using statements to delete, the code is [where peopleId in (select peopleId from people group by peopleId].

The method of removing re-query by sql:

Sql single-table / multi-table query to remove duplicate records

Single table distinct

Multi-meter group by

Group by must be placed before order by and limit, or an error will be reported.

1. Look up the redundant duplicate records in the table. Duplicate records are judged by a single field (peopleId).

Select * from peoplewhere peopleId in (select peopleId from people group by peopleId having count (peopleId) > 1)

2. Delete redundant duplicate records from the table. Duplicate records are judged according to a single field (peopleId), leaving only the records with the smallest rowid.

Delete from peoplewhere 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. Look up redundant duplicate records in the table (multiple fields)

Select * from vitae awhere (a.peopleIdmema.seq) in (select peopleId,seq from vitae group by peopleId,seq having count (*) > 1)

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

Delete from vitae awhere (a.peopleIdline 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. Look up the redundant duplicate records in the table (multiple fields), excluding the records with the smallest rowid

Select * from vitae awhere (a.peopleIdmeme 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 A

And the "name" value may be the same between different records.

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

Select Name,Count (*) From A Group By Name Having Count (*) > 1

If you also check that the gender is the same, it will be as follows:

Select Name,sex,Count (*) From A Group By Name,sex Having Count (*) > 1

(III)

Method one

Declare @ max integer,@id integerdeclare cur_rows cursor local for select main field, count (*) from table name group by main field having count (*) >; 1open cur_rowsfetch cur_rows into @ id,@maxwhile @ @ fetch_status=0beginselect @ max = @ max-1set rowcount @ maxdelete from table name where main field = @ idfetch cur_rows into @ id,@maxendclose cur_rowsset rowcount 0

Method two

"duplicate record" has two duplicate records, one is a completely duplicate record, that is, a record in which all fields are repeated, and the other is a record in which some key fields are duplicated, such as Name fields, while other fields are not necessarily duplicated or can be ignored.

1. For the first kind of repetition, it is easier to solve and use

Select distinct * from tableName

You can get a result set with no duplicate records.

If the table needs to delete duplicate records (keep 1 duplicate record), you can delete it as follows

Select distinct * into # Tmp from tableNamedrop table tableNameselect * into tableName from # Tmpdrop table # Tmp

The reason for this repetition is the poor design of the table, which can be solved by adding unique index columns.

2. This kind of duplicate problem usually requires the retention of the first record in the duplicate record. The operation method is as follows.

Suppose a duplicate field is Name,Address, and a unique result set of these two fields is required.

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 results in a result set in which Name,Address does not repeat (but with an extra autoID field, which can be omitted in the select clause when actually writing)

(4)

Duplicate query

Select * from tablename where id in (select id from tablenamegroup by idhaving count (id) > 1)

3. Look up redundant duplicate records in the table (multiple fields)

Select * from vitae awhere (a.peopleIdmema.seq) in (select peopleId,seq from vitae group by peopleId,seq having count (*) > 1)

Running will cause problems, and writing and sending things like where (a. PeopleId. Seq) won't pass!

After reading this article, I believe you have a certain understanding of sql how to re-query, want to know more related knowledge, welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.