if object_id(N'tempdb..#tb') is not null drop table #tb create table #tb([id] int, sex int , [Name] varchar(13),depart int,subdepart int,[group] int) insert #tb select 52001,0,'aaa',1,2,0 union all select 52002,1,'bbb',1,2,0 union all select 52003,0,'ccc',1,4 ,0 union all select 52004,1,'ddd',1,6 ,0 union all select 52005,0,'eee',2,1,0 union all select 52006,1,'fff',6,23,0 union all select 52007,0,'ggg',3,6 ,0 union all select 52008,1,'hhh',2,1,0 union all select 52009,0,'iii',3,6,0 union all select 52010,1,'jjj',3,6 ,0 union all select 52011,0,'kkk',1,34 ,0 union all select 52012,1,'lll',1,5,0 union all select 52013,0,'mmm',4,1,0 union all select 52014,1,'nnn',4,3 ,0 union all select 52015,0,'ooo',1,3,0 declare @Group intdeclare @id1 int declare @id2 intset @Group=1while exists ( select 1 from #tb a,#tb b where a.sex<>b.sex and a.depart=b.depart and a.[group]=0 and b.[group]=0 and a.subdepart=b.subdepart ) begin select top 1 @id1=a.id,@id2=b.id from #tb a,#tb b where a.sex<>b.sex and a.depart=b.depart and a.[group]=0 and b.[group]=0 and a.subdepart=b.subdepart
update #tb set [group]=@Group where id in (@Id1,@id2) set @Group=@Group+1 endwhile exists ( select 1 from #tb a,#tb b where a.sex<>b.sex and a.depart=b.depart and a.[group]=0 and b.[group]=0 ) begin select top 1 @id1=a.id,@id2=b.id from #tb a,#tb b where a.sex<>b.sex and a.depart=b.depart and a.[group]=0 and b.[group]=0
update #tb set [group]=@Group where id in (@Id1,@id2) set @Group=@Group+1 endwhile exists ( select 1 from #tb where [group]=0 ) begin set rowcount 1 update #tb set [group]=@Group where [Group]=0 set rowcount 0 set @Group=@Group+1 endselect * from #tb order by [group]--结果 id sex Name depart subdepart group ----------- ----------- ------------- ----------- ----------- ----------- 52001 0 aaa 1 2 1 52002 1 bbb 1 2 1 52005 0 eee 2 1 2 52008 1 hhh 2 1 2 52010 1 jjj 3 6 3 52007 0 ggg 3 6 3 52003 0 ccc 1 4 4 52004 1 ddd 1 6 4 52011 0 kkk 1 34 5 52012 1 lll 1 5 5 52013 0 mmm 4 1 6 52014 1 nnn 4 3 6 52006 1 fff 6 23 7 52009 0 iii 3 6 8 52015 0 ooo 1 3 9(所影响的行数为 15 行)
create table #tb([id] int, sex int , [Name] varchar(13),depart int,subdepart int,[group] int) insert #tb select 52001,0,'aaa',1,2,0 union all select 52002,1,'bbb',1,2,0 union all select 52003,0,'ccc',1,4 ,0 union all select 52004,1,'ddd',1,6 ,0 union all select 52005,0,'eee',2,1,0 union all select 52006,1,'fff',6,23,0 union all select 52007,0,'ggg',3,6 ,0 union all select 52008,1,'hhh',2,1,0 union all select 52009,0,'iii',3,6,0 union all select 52010,1,'jjj',3,6 ,0 union all select 52011,0,'kkk',1,34 ,0 union all select 52012,1,'lll',1,5,0 union all select 52013,0,'mmm',4,1,0 union all select 52014,1,'nnn',4,3 ,0 union all select 52015,0,'ooo',1,3,0 godeclare @i int set @i=0update a set @i=@i+1,[group]=@i from #tb a,#tb b where a.sex=0 and b.sex=1 and a.[group]=0 and b.[group]=0 and a.depart=b.depart and a.subdepart=b.subdepart update b set [group]=a.[group] from #tb a,#tb b where a.sex=0 and b.sex=1 and a.[group]>0 and b.[group]=0 and a.depart=b.depart and a.subdepart=b.subdepartupdate a set [group]=0 from #tb a where [group]>0 and not exists(select 1 from #tb where [group]=a.[group] and id<>a.id)--declare @i int --set @i=4 declare @t int set @t=@iupdate a set @i=@i+1,[group]=@i from #tb a,#tb b where a.sex=0 and b.sex=1 and a.[group]=0 and b.[group]=0 and a.depart=b.departupdate b set [group]=a.[group] from #tb a,#tb b where a.sex=0 and b.sex=1 and a.[group]>@t and b.[group]=0 and a.depart=b.departupdate a set [group]=0 from #tb a where [group]>0 and not exists(select 1 from #tb where [group]=a.[group] and id<>a.id)--declare @i int --set @i=9 update #tb set @i=@i+1,[group]=@i where [group]=0select * from #tb order by [group]drop table #tb
谢谢
drop table #tb
create table #tb([id] int, sex int , [Name] varchar(13),depart int,subdepart int,[group] int)
insert #tb select 52001,0,'aaa',1,2,0
union all select 52002,1,'bbb',1,2,0
union all select 52003,0,'ccc',1,4 ,0
union all select 52004,1,'ddd',1,6 ,0
union all select 52005,0,'eee',2,1,0
union all select 52006,1,'fff',6,23,0
union all select 52007,0,'ggg',3,6 ,0
union all select 52008,1,'hhh',2,1,0
union all select 52009,0,'iii',3,6,0
union all select 52010,1,'jjj',3,6 ,0
union all select 52011,0,'kkk',1,34 ,0
union all select 52012,1,'lll',1,5,0
union all select 52013,0,'mmm',4,1,0
union all select 52014,1,'nnn',4,3 ,0
union all select 52015,0,'ooo',1,3,0 declare @Group intdeclare @id1 int
declare @id2 intset @Group=1while exists (
select 1 from #tb a,#tb b
where a.sex<>b.sex
and a.depart=b.depart
and a.[group]=0
and b.[group]=0
and a.subdepart=b.subdepart
)
begin
select top 1 @id1=a.id,@id2=b.id from #tb a,#tb b
where a.sex<>b.sex
and a.depart=b.depart
and a.[group]=0
and b.[group]=0
and a.subdepart=b.subdepart
update #tb set [group]=@Group
where id in (@Id1,@id2) set @Group=@Group+1
endwhile exists (
select 1 from #tb a,#tb b
where a.sex<>b.sex
and a.depart=b.depart
and a.[group]=0
and b.[group]=0
)
begin
select top 1 @id1=a.id,@id2=b.id from #tb a,#tb b
where a.sex<>b.sex
and a.depart=b.depart
and a.[group]=0
and b.[group]=0
update #tb set [group]=@Group
where id in (@Id1,@id2) set @Group=@Group+1
endwhile exists (
select 1 from #tb
where [group]=0
)
begin
set rowcount 1
update #tb set [group]=@Group
where [Group]=0
set rowcount 0 set @Group=@Group+1
endselect * from #tb
order by [group]--结果
id sex Name depart subdepart group
----------- ----------- ------------- ----------- ----------- -----------
52001 0 aaa 1 2 1
52002 1 bbb 1 2 1
52005 0 eee 2 1 2
52008 1 hhh 2 1 2
52010 1 jjj 3 6 3
52007 0 ggg 3 6 3
52003 0 ccc 1 4 4
52004 1 ddd 1 6 4
52011 0 kkk 1 34 5
52012 1 lll 1 5 5
52013 0 mmm 4 1 6
52014 1 nnn 4 3 6
52006 1 fff 6 23 7
52009 0 iii 3 6 8
52015 0 ooo 1 3 9(所影响的行数为 15 行)
insert #tb select 52001,0,'aaa',1,2,0
union all select 52002,1,'bbb',1,2,0
union all select 52003,0,'ccc',1,4 ,0
union all select 52004,1,'ddd',1,6 ,0
union all select 52005,0,'eee',2,1,0
union all select 52006,1,'fff',6,23,0
union all select 52007,0,'ggg',3,6 ,0
union all select 52008,1,'hhh',2,1,0
union all select 52009,0,'iii',3,6,0
union all select 52010,1,'jjj',3,6 ,0
union all select 52011,0,'kkk',1,34 ,0
union all select 52012,1,'lll',1,5,0
union all select 52013,0,'mmm',4,1,0
union all select 52014,1,'nnn',4,3 ,0
union all select 52015,0,'ooo',1,3,0
godeclare @i int
set @i=0update a
set @i=@i+1,[group]=@i
from #tb a,#tb b
where a.sex=0 and b.sex=1 and a.[group]=0 and b.[group]=0
and a.depart=b.depart and a.subdepart=b.subdepart update b set [group]=a.[group]
from #tb a,#tb b
where a.sex=0 and b.sex=1 and a.[group]>0 and b.[group]=0
and a.depart=b.depart and a.subdepart=b.subdepartupdate a set [group]=0
from #tb a
where [group]>0 and not exists(select 1 from #tb where [group]=a.[group] and id<>a.id)--declare @i int
--set @i=4
declare @t int
set @t=@iupdate a
set @i=@i+1,[group]=@i
from #tb a,#tb b
where a.sex=0 and b.sex=1 and a.[group]=0 and b.[group]=0
and a.depart=b.departupdate b set [group]=a.[group]
from #tb a,#tb b
where a.sex=0 and b.sex=1 and a.[group]>@t and b.[group]=0
and a.depart=b.departupdate a set [group]=0
from #tb a
where [group]>0 and not exists(select 1 from #tb where [group]=a.[group] and id<>a.id)--declare @i int
--set @i=9
update #tb
set @i=@i+1,[group]=@i
where [group]=0select * from #tb order by [group]drop table #tb