sql按条件配对分组
原数据
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
select * from #tb
要求的结果
if object_id(N'tempdb..#tc') is not null
drop table #tc
create table #tc([id] int, sex int , [Name] varchar(13),depart int,subdepart int,[group] int)
insert #tb select 52001,0,'aaa',1,2,1
union all select 52002,1,'bbb',1,2,1
union all select 52003,0,'ccc',1,4 ,2
union all select 52004,1,'ddd',1,6 ,2
union all select 52005,0,'eee',2,1,6
union all select 52006,1,'fff',6,23,9
union all select 52007,0,'ggg',3,6 ,7
union all select 52008,1,'hhh',2,1,6
union all select 52009,0,'iii',3,6,5
union all select 52010,1,'jjj',3,6 ,5
union all select 52011,0,'kkk',1,34 ,4
union all select 52012,1,'lll',1,5,4
union all select 52013,0,'mmm',4,1,8
union all select 52014,1,'nnn',4,3 ,8
union all select 52015,0,'ooo',1,3,3
select * from #tc
order by [group]
`具体的规则就是，sex表示性别，要不同性别的两个人配对，两人一组，或一人一组，
配对原则，首先必须 depart 一致 ，否则，不能配对。在 depart 一致的前提下，subdepart 如果一致，则配成一对，剩下subdepart 不一致的，depart 一致也能配对，参照结果，不知道我说明白没
谢谢原数据
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
select * from #tb
要求的结果
if object_id(N'tempdb..#tc') is not null
drop table #tc
create table #tc([id] int, sex int , [Name] varchar(13),depart int,subdepart int,[group] int)
insert #tb select 52001,0,'aaa',1,2,1
union all select 52002,1,'bbb',1,2,1
union all select 52003,0,'ccc',1,4 ,2
union all select 52004,1,'ddd',1,6 ,2
union all select 52005,0,'eee',2,1,6
union all select 52006,1,'fff',6,23,9
union all select 52007,0,'ggg',3,6 ,7
union all select 52008,1,'hhh',2,1,6
union all select 52009,0,'iii',3,6,5
union all select 52010,1,'jjj',3,6 ,5
union all select 52011,0,'kkk',1,34 ,4
union all select 52012,1,'lll',1,5,4
union all select 52013,0,'mmm',4,1,8
union all select 52014,1,'nnn',4,3 ,8
union all select 52015,0,'ooo',1,3,3
select * from #tc
order by [group]
`具体的规则就是，sex表示性别，要不同性别的两个人配对，两人一组，或一人一组，
配对原则，首先必须 depart 一致 ，否则，不能配对。在 depart 一致的前提下，subdepart 如果一致，则配成一对，剩下subdepart 不一致的，depart 一致也能配对，参照结果，不知道我说明白没
谢谢zjcxc
[解决办法]
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 int
declare @id1 int
declare @id2 int
set @Group=1
while 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
end
while 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
end
while 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
end
select * 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)go 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 select *, (select count(*) from #tb where (sex=a.sex and (depart<a.depart or (depart=a.depart and (subdepart<a.subdepart or (subdepart=a.subdepart and id>a.id) ) ) ) ) ) +case when not exists(select 1 from #tb where sex!=a.sex and depart=a.depart and subdepart=a.subdepart) then case when not exists(select 1 from #tb where sex!=a.sex and depart=a.depart) then case when not exists(select 1 from #tb where sex!=a.sex) then (select count(*) from #tb) else (select count(*) from #tb where sex=a.sex) end else (select count(*) from #tb where sex=a.sex and depart=a.depart) endelse 0end idx into #1from #tb aupdate a set [group] = (select (select count(*) from #1 where sex=b.sex and (idx<b.idx or (idx=b.idx and id<b.id)) ) from #1 b where id=a.id) from #tb a select * from #tb order by [group],idgodrop table #1godrop table #tbgo
