Several Methods for filtering duplicate data using SQL statements

During development, we often encounter data with repeated records. We try again to filter Repeated Records. Below I have summarized several methods to help you.

Method 1,

 

Select identity (int, 1, 1) as id, * into # temp from tabel
Select * from # temp where id in (
Select max (id) from # emp where having count (*)> 1 group by col1, col2, col3 ...)

Instance

The structure of a table is as follows:

Id bookname cbs zz
001 asp tutorial Daquan Tsinghua University Press House Tian zhengping
002 database tutorial system tutorial Zhou aoying, Higher Education Press
003 asp Daquan Tsinghua University Press House Tian zhengping

Now I want to make a query. The fields to be searched are as follows:

Bookname, cbs, zz

Filter records by the bookname field, but display multiple fields

The query result should be

Bookname cbs zz
Asp Daquan Tsinghua University Press House Tian zhengping
Database System tutorial Zhou aoying, Higher Education Press

However, I don't know how to write SQL statements.

 

Nononono (null, null): The method can solve the problem of completely repeating. If there are several fields that are not repeated
But what should I do?
For example, if you want to display
Id bookname cbs zz new_id
(New_id is repeated for repeated records, but other records are not repeated)
And the ID displays the maximum value. How can this problem be solved.
Generally used

Select Max (id) id, max (bookname)
Bookname, max (cbs) cbs, max (zz) zz, new_id from table group by new_id

The field followed by the group by clause is the condition for you to judge repetition. For example, if only col1 is used, if the content of col1 is the same, the record is the same.

Col1 + ',' + col2 + ','... col5 federated primary key

 

Select * from table where col1 + ',' + col2 + ','... col5 in (
Select max (col1 + ',' + col2 + ','... col5) from table
Where having count (*)> 1
Group by col1, col2, col3, col4
)

/////////

Select t1.bookname, t1.zz, t1.cbs from table t1
Join (select min (id) as id, bookname from table
Group by bookname) as t2
On t1.bookname = t2.bookname and t1.id = t2.id