SQL query skills (query duplicate records)

Source: Internet
Author: User
1. -- query the duplicate value of a column (or multiple columns) (only the value of the duplicate record can be found, not the information of the entire record)
-- For example, query records with repeated item_no and item_subno
Select item_no, item_subnofrom stuinfo
Group by item_no, item_subno
Having (count (*)> 1

2. -- query records with duplicate values in a column (this method finds all duplicate records, that is, if there are two duplicate records, it finds two Records)
-- For example, query records with duplicate stuid
Select * From stuinfo
Where stuid in (
Select stuid from stuinfo
Group by stuid
Having (count (*)> 1
)

3. -- query records with duplicate values in a column (only Redundant records are displayed, that is, two records are displayed if there are three duplicate records)
-- The premise of such a score is: there must be a non-repeated column, in this example, recno
-- For example, query records with duplicate stuid
Select * From stuinfo S1
Where recno not in (
Select max (recno) from stuinfo S2
Where s1.stuid = s2.stuid
)

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.