-- Create a test environment
Create Table ttt
(
Name varchar (20 ),
ADDR varchar (20)
)
Insert ttt
Select 'zhang san', 'beijing' Union all
Select 'Li si', 'shanghai China' Union all
Select 'wang 5', 'tianjin, China' Union all
Select 'zhang san', 'sichuan province, China'
Method 1:
Select * From TTT t
Where exists (select 1 from TTT where name = T. Name and ADDR <> T. ADDR)
Method 2:
Declare @ TB table (ID int identity, name varchar (20), ADDR varchar (20 ))
Insert @ Tb (name, ADDR) Select * From ttt
Select name, ADDR from @ TB t
Where exists (select 1 from @ TB where name = T. Name and ID <> T. ID)
Method 3:
Select * From TTT t
Where
(Select count (1) From TTT where name = T. Name)> 1
Order by name
Method 4:
Select * From TTT t
Where exists (select top B. name from (select Top 2 t1.name from TTT T1
Where name = t1.name order by t1.name) B
Where B. Name = T. Name)
Order by name