高分求一个Sql语句？

[savagewang1978]

表company如下字段：
name address tel
A aaaa 5689789
A bbbb 888888888
B Aoooo 23568974
C dddddsds 8974561
B dddddd 897456666

要求：
name 中的值有一些相同，但是地址address中值不会相同

如何用一条语句查询出表company中name 重复的所有数据，必须包括所有字段查询出name, address,tel的所有值
高分求该语句

[savagewang1978]

大家的想法真是不错hdhai9451(※★開拓者...脚妞伤了☆※)开了好头，但是mgsray(我是新新新新,新来的吧) 最先给了正确答案，感谢各位参与，谢谢

[Andy__Huang]

select a.* from company a,(select name,address=max(address) from company group by name having count(*)>1)b
where a.name=b.name and a.address=b.address

[Andy__Huang]

create table company(name varchar(5),address varchar(20),tel varchar(20))
Insert into company
select 'A','aaaa','5689789'
union all select 'A','bbbb','888888888'
union all select 'B','Aoooo','23568974'
union all select 'C','dddddsds','8974561'
union all select 'B','dddddd','897456666'

select a.* from company a,(select name,address=max(address) from company group by name having count(*)>1)b
where a.name=b.name and a.address=b.address

--結果
name address tel
------------------------------
A bbbb 888888888
B dddddd 897456666

[mgsray]

select a.* from company a,(select name from company group by name having count(*)>1)b
where a.name=b.name

[mgsray]

create table company(name varchar(5),address varchar(20),tel varchar(20))
Insert into company
select 'A','aaaa','5689789'
union all select 'A','bbbb','888888888'
union all select 'B','Aoooo','23568974'
union all select 'C','dddddsds','8974561'
union all select 'B','dddddd','897456666'


select a.* from company a,(select name from company group by name having count(*)>1)b
where a.name=b.name

--result
name address tel
----- -------------------- --------------------
A aaaa 5689789
A bbbb 888888888
B Aoooo 23568974
B dddddd 897456666

[Andy__Huang]

還有一種寫法

select a.* from company a inner join
(select name,address=max(address) from company group by name having count(*)>1)b
on a.name=b.name and a.address=b.address

--結果
name address tel
------------------------------
A bbbb 888888888
B dddddd 897456666

[Andy__Huang]

不好意思﹐應該去掉address的相等


create table company(name varchar(5),address varchar(20),tel varchar(20))
Insert into company
select 'A','aaaa','5689789'
union all select 'A','bbbb','888888888'
union all select 'B','Aoooo','23568974'
union all select 'C','dddddsds','8974561'
union all select 'B','dddddd','897456666'

select a.* from company a,(select name,address=max(address) from company group by name having count(*)>1)b
where a.name=b.name


--結果
name address tel
------------------------------
A aaaa 5689789
A bbbb 888888888
B Aoooo 23568974
B dddddd 897456666


select a.* from company a inner join
(select name,address=max(address) from company group by name having count(*)>1)b
on a.name=b.name

--結果
name address tel
------------------------------
A aaaa 5689789
A bbbb 888888888
B Aoooo 23568974
B dddddd 897456666

[ntflying]

select * from company where [name]
in (select [name] from company
group by [name]
having count(*) > 1)

[xiaoxiangqing]

create table company(name varchar(10),address varchar(20),tel varchar(20))
Insert into company
select 'A','aaaa','5689789'
union all select 'A','bbbb','888888888'
union all select 'B','Aoooo','23568974'
union all select 'C','dddddsds','8974561'
union all select 'B','dddddd','897456666'
go

select * from company
/*
name address tel
A aaaa 5689789
A bbbb 888888888
B Aoooo 23568974
C dddddsds 8974561
B dddddd 897456666
*/

--方法一
select name,address,tel from company a where exists(select * from company group by name having count(*)>1 and a.name=name)
/*
name address tel
A aaaa 5689789
A bbbb 888888888
B Aoooo 23568974
B dddddd 897456666
*/

--方法二
select a.* from company a join (select name from company group by name having count(*)>1) b on a.name=b.name
/*
name address tel
A aaaa 5689789
A bbbb 888888888
B Aoooo 23568974
B dddddd 897456666
*/

[savagewang1978]

不错，给分