Miscellaneous _ display duplicate records

Requirement

A table may have n records. How can I display duplicate records?

SQL> Create tablet1 (

2 ID number,

3 namevarchar2 (20)

4 );

 

Table created.

 

SQL> insert into T1 values (1, 'zhang yun ');

 

1 row created.

 

SQL> insert into T1 values (2, 'Ye yingcai ');

 

1 row created.

 

SQL> insert into T1 values (3, 'single-origin status ');

 

1 row created.

 

SQL> insert into T1 values (4, 'ant ');

 

1 row created.

 

SQL> insert into T1 values (5, 'Yao fengming ');

 

1 row created.

 

SQL> insert into T1 values (6, 'wu Cheng ');

 

1 row created.

 

SQL> insert into T1 values (7, 'zhang yun ');

 

1 row created.

 

SQL> insert into T1 values (8, 'wu Cheng ');

 

1 row created.

 

SQL> insert into T1 values (9, 'Ye yingcai ');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from T1;

 

ID name

------------------------------

1 Zhang Yun

2 ye yingcai

3 single country

4 Yi yaning

5. Yao fengming

6. Wu licheng

7 Zhang Yun

8. Wu licheng

9 ye yingcai

 

Analysis

First, we need to count the names and group duplicate names to find duplicate records.

SQL> select namefrom T1 group by name having count (name)> 1;

 

Name

--------------------

Wu licheng

Ye yingcai

Zhang Yun

 

Question 2

You have found out the duplicates, but how do I know that they are duplicates? If there are too many duplicates, you cannot see them directly.

SQL> select name, count (name) from T1 group by name having count (name)> 1;

(The total number is greater than 1, proving that there are duplicates)

 

Name count (name)

-------------------------------

Wu licheng 2

Ye yingcai 2

Zhang Yun 2

 

Third question

You have recorded repeated records, but how do I know what these records are?

SQL> select ID, name

2 from T1

3 where name in (Select name from T1 group by name having count (name)> 1)

4 order by name;

 

ID name

------------------------------

2 ye yingcai

9 ye yingcai

6. Wu licheng

8. Wu licheng

1 Zhang Yun

7 Zhang Yun

This article from the "happy life of cats and bears" blog, please be sure to keep this source http://bearlovecat.blog.51cto.com/1293914/1554409

Miscellaneous _ display duplicate records