) SQL query duplicate records

Table stuinfo, which has three fields: recno (auto-increment), stuid, and stuname

CreateSQLThe statement is as follows:

Create Table [stuinfo] (
[Recno] [int] identity (1, 1) not null,
[Stuid] [varchar] (10) Collate chinese_prc_ci_as not null,
[Stuname] [varchar] (10) Collate chinese_prc_ci_as not null
) On [primary]
Go

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 duplicate stuid and stuname
Select stuid, stuname from stuinfo
Group by stuid, stuname
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
)

The following figure shows all duplicate records.SQLStatement:
Method 1:
SQL > Select * From table_name A where rowid> (
Select min (rowid) from table_name B
Where a. key_values = B. key_values );

Method 2:
SQL > Select * From table_name T1
Where exists (select 'x' from table_name T2
Where t2.key _ value1 = t1.key _ value1
And t2.key _ value2 = t1.key _ value2
And t2.rowid> t1.rowid );