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  
unio