The SQL read information is not repeated.

Source: Internet
Author: User
Tags rowcount

-- Process duplicate table records (query and delete)
/*************************************** **************************************** **************************************** *******************************
1. Repeat records with the same num and name. Only one record is retained if there is no size relationship.
2. if the name is the same and the ID has a size relationship, keep one of the records large or small.

**************************************** **************************************** **************************************** ******************************/

-- 1. Used to query repeat processing records (if the column has no size relationship, 2000 use the auto-incrementing column and temporary table for processing, and sql2005 uses the row_number function for processing)

-->

If not object_id ('tempdb .. # t') is null
Drop table # T
Go
Create Table # T ([ID] int, [name] nvarchar (1), [memo] nvarchar (2 ))
Insert # T
Select 1, n 'A', N 'a1' Union all
Select 2, N 'A', N 'a2 'Union all
Select 3, N 'A', N 'a3 'Union all
Select 4, N 'B', N 'b1 'Union all
Select 5, N 'B', N 'b2'
Go

-- Records with the smallest IDs of I and name (1, 2, 3 are recommended). Method 3 is more efficient than 1 and 2 in sql05.
Method 1:
Select * from # t a where not exists (select 1 from # t where name = A. Name and ID
Method 2:
Select. * From # t a join (select Min (ID) ID, name from # T group by name) B on. name = B. name and. id = B. ID

Method 3:
Select * from # t a where id = (select Min (ID) from # t where name = A. Name)

Method 4:
Select. * From # t a join # t B on. name = B. name and. id> = B. id group by. ID,. name,. memo having count (1) = 1

Method 5:
Select * from # t a group by ID, name, memo having id = (select Min (ID) from # t where name = A. Name)

Method 6:
Select * from # t a where (select count (1) from # t where name = A. Name and ID
Method 7:
Select * from # t a where id = (select top 1 ID from # t where name = A. Name order by ID)

Method 8:
Select * from # t a where Id!> All (select ID from # t where name = A. Name)

Method 9 (Note: It is available when ID is unique ):
Select * from # t a where ID in (select Min (ID) from # T group by name)

-- Sql2005:

Method 10:
Select ID, name, memo from (select *, min (ID) over (partition by name) as minid from # t a) t where id = minid

Method 11:

Select ID, name, memo from (select *, row_number () over (partition by name order by ID) as minid from # t a) t where minid = 1

Result:
/*
ID name memo
-------------------
1 A A1
4 B B1

(2 rows affected)
*/

-- II. records with the same ID and maximum name, opposite to Min:
Method 1:
Select * from # t a where not exists (select 1 from # t where name = A. Name and ID> A. ID)

Method 2:
Select. * From # t a join (select max (ID) ID, name from # T group by name) B on. name = B. name and. id = B. id order by ID

Method 3:
Select * from # t a where id = (select max (ID) from # t where name = A. Name) order by ID

Method 4:
Select. * From # t a join # t B on. name = B. name and. ID <= B. id group by. ID,. name,. memo having count (1) = 1

Method 5:
Select * from # t a group by ID, name, memo having id = (select max (ID) from # t where name = A. Name)

Method 6:
Select * from # t a where (select count (1) from # t where name = A. Name and ID> A. ID) = 0

Method 7:
Select * from # t a where id = (select top 1 ID from # t where name = A. Name order by id desc)

Method 8:
Select * from # t a where ID!
Method 9 (Note: It is available when ID is unique ):
Select * from # t a where ID in (select max (ID) from # T group by name)

-- Sql2005:

Method 10:
Select ID, name, memo from (select *, max (ID) over (partition by name) as minid from # t a) t where id = minid

Method 11:
Select ID, name, memo from (select *, row_number () over (partition by name order by id desc) as minid from # t a) t where minid = 1

Result 2:
/*
ID name memo
-------------------
3 A A3
5 B B2

(2 rows affected)
*/

-- 2. When deleting duplicate records with a size relationship, keep one of the records large or small.

--> (Roy) generate a sequence number

If not object_id ('tempdb .. # t') is null
Drop table # T
Go
Create Table # T ([ID] int, [name] nvarchar (1), [memo] nvarchar (2 ))
Insert # T
Select 1, n 'A', N 'a1' Union all
Select 2, N 'A', N 'a2 'Union all
Select 3, N 'A', N 'a3 'Union all
Select 4, N 'B', N 'b1 'Union all
Select 5, N 'B', N 'b2'
Go

-- Record with the minimum ID for I and name (1, 2, 3 is recommended), and the minimum record is retained
Method 1:
Delete A from # t a where exists (select 1 from # t where name = A. Name and ID
Method 2:
Delete A from # t a left join (select Min (ID) ID, name from # T group by name) B on. name = B. name and. id = B. ID where B. ID is null

Method 3:
Delete A from # t a where id not in (select Min (ID) from # t where name = A. Name)

Method 4 (Note: It is available when ID is unique ):
Delete A from # t a where id not in (select Min (ID) from # T group by name)

Method 5:
Delete A from # t a where (select count (1) from # t where name = A. Name and ID 0

Method 6:
Delete A from # t a where ID <> (select top 1 ID from # t where name = A. Name order by ID)

Method 7:
Delete A from # t a where ID> Any (select ID from # t where name = A. Name)

Select * from # T

Result:
/*
ID name memo
-------------------
1 A A1
4 B B1

(2 rows affected)
*/

-- II. Keep the largest record with the same ID as name:

Method 1:
Delete A from # t a where exists (select 1 from # t where name = A. Name and ID> A. ID)

Method 2:
Delete A from # t a left join (select max (ID) ID, name from # T group by name) B on. name = B. name and. id = B. ID where B. ID is null

Method 3:
Delete A from # t a where id not in (select max (ID) from # t where name = A. Name)

Method 4 (Note: It is available when ID is unique ):
Delete A from # t a where id not in (select max (ID) from # T group by name)

Method 5:
Delete A from # t a where (select count (1) from # t where name = A. Name and ID> A. ID)> 0

Method 6:
Delete A from # t a where ID <> (select top 1 ID from # t where name = A. Name order by id desc)

Method 7:
Delete A from # t a where ID

Select * from # T
/*
ID name memo
-------------------
3 A A3
5 B B2

(2 rows affected)
*/

-- 3. Process duplicate values when deleting duplicate records with no size relationship

--> (Roy) generate a sequence number

If not object_id ('tempdb .. # t') is null
Drop table # T
Go
Create Table # T ([num] int, [name] nvarchar (1 ))
Insert # T
Select 1, n 'A' Union all
Select 1, n 'A' Union all
Select 1, n 'A' Union all
Select 2, N 'B' Union all
Select 2, N 'B'
Go

Method 1:
If object_id ('tempdb .. # ') is not null
Drop table #
Select distinct * into # From # t -- exclude duplicate record result set to generate a temporary table #

Truncate table # t -- clear the table

Insert # T select * from # -- insert temporary table # into Table # T

-- View results
Select * from # T

/*
Num name
---------------
1
2 B

(2 rows affected)
*/

-- Method 2 after re-executing the test data
Method 2:

Alter table # t add ID int identity -- add ID column
Go
Delete A from # t a where exists (select 1 from # t where num = A. Num and name = A. Name and ID> A. ID) -- only one record is retained.
Go
Alter table # t drop column id -- delete the ID column

-- View results
Select * from # T

/*
Num name
---------------
1
2 B

(2 rows affected)

*/

-- Method 3 after re-executing the test data
Method 3:
Declare roy_cursor cursor local
Select count (1)-1, num, name from # T group by num, name having count (1)> 1
Declare @ con int, @ num int, @ name nvarchar (1)
Open roy_cursor
Fetch next from Maid into @ con, @ num, @ name
While @ fetch_status = 0
Begin
Set rowcount @ con;
Delete # t where num = @ num and name = @ name
Set rowcount 0;
Fetch next from Maid into @ con, @ num, @ name
End
Close roy_cursor
Deallocate roy_cursor

-- View results
Select * from # T
/*
Num name
---------------
1
2 B

(2 rows affected)
*/

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.