哎 select b from table group by b having count(b) > 1
create table #a (id int,id1 varchar(10),id2 varchar(10),a varchar(10),b varchar(10)) insert into #a select 1,'11','10','','' insert into #a select 2,'22','11','','' insert into #a select 1,'22','10','','' insert into #a select 2,'13','11','',''select id1,count(*) from #a group by id1 having count(*)>1 drop table #a
其中id是自动增加的该列属于表结构,不可以用update
b字段相同的话,id会有多个,是把id设置成最大的还是最小的呢?
create table #a (id int,id1 varchar(10),id2 varchar(10),a varchar(10),b varchar(10)) insert into #a select 1,'11','10','','' insert into #a select 2,'22','11','','' insert into #a select 3,'22','10','','' insert into #a select 4,'13','11','','' insert into #a select 5,'22','10','','' insert into #a select 6,'22','11','','' select * from #aupdate #a set id=(select max(id) from #a where id1=a.id1) from #a a where id1 in (select id1 from #a group by id1 having count(*)>1)select * from #adrop table #a
from table
group by b
having (b) > 1
select b
from table
group by b
having count(b) > 1
insert into #a select 1,'11','10','',''
insert into #a select 2,'22','11','',''
insert into #a select 1,'22','10','',''
insert into #a select 2,'13','11','',''select id1,count(*) from #a
group by id1
having count(*)>1
drop table #a
insert into #a select 1,'11','10','',''
insert into #a select 2,'22','11','',''
insert into #a select 3,'22','10','',''
insert into #a select 4,'13','11','',''
insert into #a select 5,'22','10','',''
insert into #a select 6,'22','11','',''
select * from #aupdate #a
set id=(select max(id) from #a where id1=a.id1)
from #a a
where id1 in
(select id1 from #a group by id1 having count(*)>1)select * from #adrop table #a