表:a id name 11 aaaa 11 bbbb 11 cccc 22 dddd 22 eeee 22 ffff
如何将表中的相同id号的第一条记录取出来?即: id name 11 aaaa 22 dddd CREATE TABLE #a ( [id] [char] (10), [name] [char] (10) )insert into #a(id,name) values('11','aaaa') insert into #a(id,name) values('11','bbbb') insert into #a(id,name) values('11','cccc') insert into #a(id,name) values('22','dddd') insert into #a(id,name) values('22','eeee') insert into #a(id,name) values('22','ffff') select * from #a b where name=(select top 1 name from #a where id=b.id)drop table #aid name ---------- ---------- 11 aaaa 22 dddd (所影响的行数为 2 行) CREATE TABLE #a ( [id] [char] (10), [name] [char] (10) )insert into #a(id,name) values('11','aaaa') insert into #a(id,name) values('11','bbbb') insert into #a(id,name) values('11','cccc') insert into #a(id,name) values('22','dddd') insert into #a(id,name) values('22','eeee') insert into #a(id,name) values('22','ffff') select id1=identity(int,1,1),* into #t from #a go select id,name from #t where id1 in(select min(id1) from #t group by id)drop table #a drop table #tid name ---------- ---------- 11 aaaa 22 dddd (所影响的行数为 2 行)
select * into #t from a b where name=(select top 1 name from a where id=b.id)truncate table ainsert into a select * from #t
select * into #t from a b where name=(select top 1 name from a where a.id=b.id)insert into a select * from #t
select distinct * into #t from table1truncate table table1insert into table1 select * from #t
加个ID1主键 select * from tablename where id1 in(select min(id) from tablename group by id1)
建立临时表可以解决,下面的方法比select distinct * into #t from table1truncate table table1insert into table1 select * from #t
id name
11 aaaa
11 bbbb
11 cccc
22 dddd
22 eeee
22 ffff
如何将表中的相同id号的第一条记录取出来?即:
id name
11 aaaa
22 dddd
CREATE TABLE #a (
[id] [char] (10),
[name] [char] (10)
)insert into #a(id,name) values('11','aaaa')
insert into #a(id,name) values('11','bbbb')
insert into #a(id,name) values('11','cccc')
insert into #a(id,name) values('22','dddd')
insert into #a(id,name) values('22','eeee')
insert into #a(id,name) values('22','ffff') select * from #a b
where name=(select top 1 name from #a where id=b.id)drop table #aid name
---------- ----------
11 aaaa
22 dddd (所影响的行数为 2 行)
CREATE TABLE #a (
[id] [char] (10),
[name] [char] (10)
)insert into #a(id,name) values('11','aaaa')
insert into #a(id,name) values('11','bbbb')
insert into #a(id,name) values('11','cccc')
insert into #a(id,name) values('22','dddd')
insert into #a(id,name) values('22','eeee')
insert into #a(id,name) values('22','ffff') select id1=identity(int,1,1),* into #t from #a
go
select id,name from #t where id1 in(select min(id1) from #t group by id)drop table #a
drop table #tid name
---------- ----------
11 aaaa
22 dddd (所影响的行数为 2 行)
where name=(select top 1 name from a where id=b.id)truncate table ainsert into a select * from #t
where name=(select top 1 name from a where a.id=b.id)insert into a select * from #t
select * from tablename where id1 in(select min(id) from tablename group by id1)