如何取分组后的第一条记录

[littlerainsnow]

insert into #t select '123456','邯郸','网络部','编辑'
union all select '544544','邯郸','网络部','技术'
union all select '344544','邯郸','网络部','技术'
union all select '144576','邯郸','网络部','主任'
union all select '144574','邯郸','网络部','主编'
union all select '124574','邯郸','网络部','编辑'


得到

teachid school workbumen workpost
123456 邯郸 网络部 编辑
544544 邯郸 网络部 技术
144576 邯郸 网络部 主任
144574 邯郸 网络部 主编


谢谢了

[dawugui]

如何将表中的相同ID号的第一条记录取出来

表：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 #a

id 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 #t

id name
---------- ----------
11 aaaa
22 dddd

（所影响的行数为 2 行）

[冷箫轻笛]

select identity(int,1,1) as id ,*
insert into #tt
from t

select * from #tt a
where not exists(select 1 from #tt where school = a.school and workbumen = a.workbumen and id < a.id)

drop table #tt

[dawugui]

select * from TB b
where workpost=(select top 1 workpost from TB where teachid = b.teachid)

[userqin]

select * from
( select * ,isnull((select count(*) from #t where school=a.school and workbumen=a.workbumen and workpost=a.workpost and teachid<=a.teachid),0) as count from #t a group by school,teachid,workbumen,workpost) b where b.count<=1