Delete duplicate rows of multiple fields keep maximum minimum row

There is no unique constraint on the design of a key field before the table, resulting in occasional repeated records when the insert operation is performed accidentally, followed by a unique constraint, and the duplicate record must be deleted because there is already a duplicate record that cannot be added.

Read some of the online methods of deleting duplicate records (as if they were reproduced in the same article, at least more than 10 read the same content), one of the links: http://blog.csdn.net/anya/article/details/6407280

You now need to establish a multi-field unique constraint (non-primary key), the method in the article is not possible, anyway, in SQL Server 2012 is not able to pass. Rewrite the following, as follows:

1, query duplicate records:

[SQL]View Plaincopy

2. SELECT * from dbo. Table T
4. WHERE EXISTS (SELECT field 1, Field 2, Field 3 from dbo. Tablesign WHERE field 1= t. Field 1 and field 2= t. Field 2
6. and field 3 = T. Field 3 GROUP by Field 1, Field 2, Field 3 having COUNT (*) > 1)
8. and T.selfid not in (theSELECT MIN (selfid) from dbo. Table GROUP by Field 1, Field 2, Field 3 having COUNT (*) > 1)
12. Where: Field 1, Field 2, Field 3 refers to the three fields that need to establish a unique constraint, selfid refers to one of the self-increment fields in tables table.

2. Delete duplicate records and keep only the Selfid minimum records, that is, the first inserted records:

[SQL]View Plaincopy

2. DELETE from dbo. Tablesign
4. WHERE Selfid in
8. (SELECT Selfid from dbo. ) Table T
12. WHERE EXISTS (SELECT field 1, Field 2, Field 3 from dbo. Tablesign WHERE field 1= t. Field 1 and field 2= t. Field 2
14. and field 3 = T. Field 3 GROUP by Field 1, Field 2, Field 3 having COUNT (*) > 1)
16. and T.selfid not in (theSELECT MIN (selfid) from dbo. Table GROUP by Field 1, Field 2, Field 3 having COUNT (*) > 1))

The types of joins in SQL can be divided into the following categories:

1) An inner join displays only the joins of the matching rows in the two joined tables.

2) Outer joins even include joins in rows that do not have related rows in the join table. You can create three variations of an outer join to specify which unmatched rows are included:

• 2-1) left Outward join
• 2-2) right outward join
• 2-3) Full outer join
• 3) Cross join
  其中值得补充的是自联接
• 4) self-join

A table can be joined to itself by a self-join. For example, you can use a self-join to find a table that uses the same email user.

Now the students table data is as follows:

Stuid Sname Semail
----------- ---------- --------------------------------------------------
1 Zhang Fei [email protected]
2 Liu Bei [email protected]
3 Guan Yu [email protected]
4 Zhao Yun [email protected]
5 Chaoxiaoyun [email protected]

How do I find the name of a user with the same email?

Because this query involves a join of the students table to itself, the students table is displayed in both roles. To differentiate between the two roles, you must provide two different aliases (St1 and St2) for the students table in the FROM clause. These aliases are used to qualify column names in the remaining queries.

Select St1.sname,st2.sname,st1.semail
From students st1 INNER JOIN students ST2
On St1.stuid=st2.stuid
where St1.semail=st2.semail

The following is the result set:

Sname sname Semail
---------- ---------- --------------------------------------------------
Zhang Fei Zhang Fei [email protected]
Liu Bei Liu Bei [email protected]
Guan Yu Guan yu [email protected]
Zhaoyun [email protected]
Shora [email protected]

(5 rows affected)

To eliminate exactly the same rows, you must do so

Select St1.sname,st2.sname,st1.semail
From students st1 INNER JOIN students ST2
On St1.stuid<>st2.stuid--note that this is not equal
where St1.semail=st2.semail

The following is the result set:

Sname sname Semail
---------- ---------- --------------------------------------------------
Showing [email protected]
Shanyun [email protected]

(2 rows affected)

To eliminate the exact order of the user names in the results, reverse the same line (in reverse order)
Select St1.sname,st2.sname,st1.semail
From students st1 INNER JOIN students ST2
On St1.stuid>st2.stuid
where St1.semail=st2.semail

The following is the result set:

Sname sname Semail
---------- ---------- --------------------------------------------------
Showing [email protected]

(1 rows affected)

This is the goal
-------------------------------------------------------------------------------------- ---------
A table to connect to itself, called a self-connected

question: A netizen put forward such a SQL topic, said oneself thought for a long time did not solve, I see, this is not very simple

but in the Query Analyzer debugging for half a day the original problem is not that simple

There is a student's table, there is a study number

student score three fields. Use a SQL query to get the top two results for each course

Study number Homework number student results

1 1

2 1 98

3 1

4 2

5 2

6 2

7 3

8 3

9 3

Workaround

SELECT DISTINCT Student table 1.*

From
Student table Student table 1 INNER JOIN

Student Table Student Table
2 on Student table 1. Study number
in

(SELECT TOP 2 Students ' table. School Number

From
student Table

WHERE Student table. Homework Number = Student Table 1. Homework Number

ORDER by student grade DESC)

Query Results

Study number Homework number student results

1 1

2 1 98

4 2

6 2

7 3

9 3

Delete duplicate rows of multiple fields keep maximum minimum row