sql语句在线等!!!!!!!

itwanderer 2010-09-17 04:32:30
在一张表中 有两列 name和id name有重复的 id没有重复的 我想查询出name重复列的id怎么查
...全文
114 点赞 收藏 16
写回复
16 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
zn85600301 2010-09-17
[Quote=引用 15 楼 sunjigen803 的回复:]
SELECT DISTINCT NAME,ID FROM TABLE这样效率应该是最高的
[/Quote]
你去看看 SQL优化的基本要求 DISTINCT是不是影响效率的
回复
sunjigen803 2010-09-17
SELECT DISTINCT NAME,ID FROM TABLE这样效率应该是最高的
回复
zn85600301 2010-09-17
[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]
总算有个比较完善的了 呵呵
回复
lihuan2008a 2010-09-17
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
回复
sq_ling 2010-09-17
select id from table where id_name in (select id_name from table b group by id_name having (count(*)) >1)
回复
Sharesin 2010-09-17
表自身关联
回复
crazylaa 2010-09-17
select id,name from tbl where exists(select 1 from tbl b where b.id<>tbl.id and b.name=tbl.name)
回复
菖蒲芭芭 2010-09-17
[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 = '你想查看的名字'
回复
WANGYQ_412 2010-09-17
表自关联!
回复
wfqqwer5213 2010-09-17

select a.id  from a ,(select name  from a group by name having count(1)>1) t where a.name=t.name
回复
yangzl39 2010-09-17
select id,count(name) from user
group by name having count(name)>1
回复
zn85600301 2010-09-17
额 楼上2位这种写法
你起码 把 a.id <> b.id 加上吧
回复
jnhcd 2010-09-17
SELECT A.ID,A.NAME
FROM TABLE A,TABLE B
WHERE A.NAME = B.NAME

看看结果如何,看是否要DISTINCT
回复
扯出一片天 2010-09-17
String strSQL = "select distinct a.ID,a.Name from Table as a,Table as b where a.Name=b.Name"
回复
zn85600301 2010-09-17
select id from user where (select count(*) from user where name ='+name+') > 1
回复
Destiny_ning 2010-09-17
去sql区发帖
回复
相关推荐
发帖
Web 开发
创建于2007-09-28

8.0w+

社区成员

Java Web 开发
申请成为版主
帖子事件
创建了帖子
2010-09-17 04:32
社区公告
暂无公告