Delete duplicate rows of multiple fields keep maximum minimum row

Source: Internet
Author: User

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
  1. SELECT * from dbo. Table T
  2. WHERE EXISTS (SELECT field 1, Field 2, Field 3 from dbo. Tablesign WHERE field 1= t. Field 1 and field 2= t. Field 2
  3. and field 3 = T. Field 3 GROUP by Field 1, Field 2, Field 3 having COUNT (*) > 1)
  4. and T.selfid not in (theSELECT MIN (selfid) from dbo. Table GROUP by Field 1, Field 2, Field 3 having COUNT (*) > 1)
  5. 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
    1. DELETE from dbo. Tablesign
    2. WHERE Selfid in
    3. (SELECT Selfid 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
    5. and field 3 = T. Field 3 GROUP by Field 1, Field 2, Field 3 having COUNT (*) > 1)
    6. 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

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.