How to use SQL statements to query records with the same value under a certain field in the database

Today I received a task with a student information table (Excel table) containing more than 10 thousand records. Now I want to import this table to the database and set the student ID as the primary key, but now the student ID in this table has repeated records. I must first find these repeated records and then filter them. After research, the problem is finally solved.
The above problem is actually to query records with duplicate Field Values in the database table. Here, we omit how to import an Excel table to the database, this article only describes how to use SQL to query records with the same value under a certain field in the database.

Assume that the database table is named student and contains the Sno (student ID) and ID (ID card) fields. Here we provide two query methods:

Method 1: query by student ID and ID card fields (highly efficient database execution, recommended)

Copy codeThe Code is as follows:
SELECT *
FROM student AS
WHERE (Sno IN (SELECT Sno FROM student as B where a. ID <> B. ID ))

Method 2: query by student ID field only (Database execution efficiency is low, not recommended)

Copy codeThe Code is as follows:
SELECT *
FROM student AS
WHERE (select count (*) FROM student WHERE Sno = A. Sno)> 1

From the running results, method 1 and method 2 are the same, but the execution efficiency is completely different. It takes only a few seconds for method 1 to execute more than 10 thousand pieces of data. method 2 takes several minutes, there is also a crash. I don't know if there are other methods. If you have any experts, please advise !!!