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 邯郸 网络部 主编
谢谢了
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 邯郸 网络部 主编
谢谢了
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 行)
insert into #tt
from tselect * 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
where workpost=(select top 1 workpost from TB where teachid = b.teachid)
( 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