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

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 people

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

Select * from vitae a

Where (a.peopleIdline a.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 a

Where (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 a

Where (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)

(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 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 main field = @ id

Fetch cur_rows into @ id,@max

End

Close cur_rows

Set 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 tableName

Drop table tableName Wuxi Bohai Hospital https://yyk.familydoctor.com.cn/20612/

Select * into tableName from # Tmp

Drop 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 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 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 tablename

Group by id

Having count (id) > 1

)

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

Select * from vitae a

Where (a.peopleIdline a.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!

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.