sql语句在线等！！！！！！！

[itwanderer]

在一张表中 有两列 name和id name有重复的 id没有重复的 我想查询出name重复列的id怎么查

[Neil Yan]

去sql区发帖

[zn85600301]

select id from user where (select count(*) from user where name ='+name+') > 1

[扯出一片天]

String strSQL = "select distinct a.ID,a.Name from Table as a,Table as b where a.Name=b.Name"

[jnhcd]

SELECT A.ID,A.NAME
FROM TABLE A,TABLE B
WHERE A.NAME = B.NAME

看看结果如何,看是否要DISTINCT

[zn85600301]

额 楼上2位这种写法
你起码 把 a.id <> b.id 加上吧

[yangzl39]

select id,count(name) from user
group by name having count(name)>1

[wfqqwer5213]

select a.id  from a ,(select name  from a group by name having count(1)>1) t where a.name=t.name

[WANGYQ_412]

表自关联！

[菖蒲老先生]

[Quote=引用 2 楼 zn85600301 的回复:]
select id from user where (select count(*) from user where name ='+name+') > 1
[/Quote]

还要加个条件吧

select id, name
  from user
 where (select count(*) from user where name = '你想查看的名字') > 1
   and name = '你想查看的名字'

[crazylaa]

select id,name from tbl where exists(select 1 from tbl b where b.id<>tbl.id and b.name=tbl.name)

[Sharesin]

表自身关联

[sq_ling]

select id from table where id_name in （select id_name from table b group by id_name having (ｃｏｕｎｔ（＊）) >１）

[lihuan2008a]

SQL 2005
create table Test (Id int identity(1,1),name varchar(50))

insert into Test(name) values('Test1');
insert into Test(name) values('Test1');
insert into Test(name) values('Test2');


SELECT A.ID,A.NAME FROM Test A,Test B WHERE A.NAME = B.NAME and A.id<>B.id

[zn85600301]

[Quote=引用 13 楼 lihuan2008a 的回复:]
SQL 2005
create table Test (Id int identity(1,1),name varchar(50))

insert into Test(name) values('Test1');
insert into Test(name) values('Test1');
insert into Test(name) values('Test2');


S……
[/Quote]
总算有个比较完善的了 呵呵

[sunjigen803]

SELECT DISTINCT NAME,ID FROM TABLE这样效率应该是最高的

[zn85600301]

[Quote=引用 15 楼 sunjigen803 的回复:]
SELECT DISTINCT NAME,ID FROM TABLE这样效率应该是最高的
[/Quote]
你去看看 SQL优化的基本要求 DISTINCT是不是影响效率的