select name
,address
from 表 t
where (select count(1) from 表 where address=t.address)>1
order by address,name
,address
from 表 t
where (select count(1) from 表 where address=t.address)>1
order by address,name
,address
from 表 order by address,name
(
id int,
name varchar(10),
address varchar(100)
)
insert @tb
select 1,'AAA','人民路' union
select 2,'BBB','人民路' union
select 3,'ccc','光明路' union
select 4,'ddd','海洋路' --查询
select name
,address
from @tb t
where (select count(1) from @tb where address=t.address)>1
order by address,name--结果
/*
name address
---------- ------------
AAA 人民路
BBB 人民路(2 row(s) affected)
*/