一个应该简单的问题
有以下表结构：
id value1 value2
a 1 1
a 2 2
a 1 1
b 3 3
b 2 2
b 1 1
c 1 1
c 2 2
c 1 1
我想从各id中取出各自的第一条纪录即以下结果：
id value1 value2
a 1 1
b 3 3
c 1 1
应该怎么写呢？大家帮帮忙
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(id varchar(1),value1 int,value2 int)
insert into tb(id,value1,value2) values( 'a ',1,1)
insert into tb(id,value1,value2) values( 'a ',2,2)
insert into tb(id,value1,value2) values( 'a ',1,1)
insert into tb(id,value1,value2) values( 'b ',3,3)
insert into tb(id,value1,value2) values( 'b ',2,2)
insert into tb(id,value1,value2) values( 'b ',1,1)
insert into tb(id,value1,value2) values( 'c ',1,1)
insert into tb(id,value1,value2) values( 'c ',2,2)
insert into tb(id,value1,value2) values( 'c ',1,1)
go
select id1=identity(int,1,1) , * into test from tb
select a.id,a.value1,a.value2 from test a,
(select id,min(id1) id1 from test group by id) b
where a.id = b.id and a.id1 = b.id1
drop table tb,test
/*
id value1 value2
---- ----------- -----------
a 1 1
b 3 3
c 1 1
（所影响的行数为 3 行）
*/
[解决办法]
CREATE TABLE #a ( [id] [char] (10), [value1] [int] ,[value2] [int] )
insert into #a(id,value1,value2) values( 'a ',1,1)
insert into #a(id,value1,value2) values( 'a ',2,2)
insert into #a(id,value1,value2) values( 'a ',1,1)
insert into #a(id,value1,value2) values( 'b ',3,3)
insert into #a(id,value1,value2) values( 'b ',2,2)
insert into #a(id,value1,value2) values( 'b ',1,1)
insert into #a(id,value1,value2) values( 'c ',1,1)
insert into #a(id,value1,value2) values( 'c ',2,2)
insert into #a(id,value1,value2) values( 'c ',1,1)
select id1=identity(int,1,1),* into #t from #a
select id,value1,value2 from #t a
where not exists(select 1 from #t
where a.id = id and a.id1 > id1)
drop table #a
drop table #t
/*
id value1 value2
---------- ----------- -----------
a 1 1
b 3 3
c 1 1
*/
