表结构如下:部门ID,姓名,姓别其中姓名用1表示男,2表示女随机组合规则:
将两个人组合在一起；属于同一部门的人不能组合在一起；因为数据表中的男女人数可能不会相等，所以需要先尽量先组合一男一女在一起，然后剩下的再组合；当然，如果数据记录为奇数，则有一人是放单的。组合后表的结构只有三列：
ID流水号 组合一 组合二
比如：0001 张三（男） 王芳（女）一般先男后女，姓名后放性别。谢谢
将两个人组合在一起；属于同一部门的人不能组合在一起；因为数据表中的男女人数可能不会相等，所以需要先尽量先组合一男一女在一起，然后剩下的再组合；当然，如果数据记录为奇数，则有一人是放单的。组合后表的结构只有三列：
ID流水号 组合一 组合二
比如：0001 张三（男） 王芳（女）一般先男后女，姓名后放性别。谢谢
insert into t
select 1,'张三',1 union
select 2,'王芳',2 union
select 3,'李丽',2 union
select 4,'王五',1 union
select 5,'赵灵',1 union
select 6,'康铮',2select id = identity(int,1,1),name + '(男)' as 组合1 into b from t where sex = 1 order by newid()
select id = identity(int,1,1),name + '(女)' as 组合2 into g from t where sex = 2 order by newid()
--(select name + '(女)' as 组合1 from @t where sex = 2) bselect b.*,g.组合2 from b inner join g on b.id = g.iddrop table b
drop table g
drop table t
insert into t
select 1,'张三',1 union
select 2,'王芳',2 union
select 3,'李丽',2 union
select 4,'王五',1 union
select 5,'赵灵',1 union
select 6,'康铮',2select id = identity(int,1,1),name + '(男)' as 组合1,depId into b from t where sex = 1 order by newid()
select id = identity(int,1,1),name + '(女)' as 组合2,depId into g from t where sex = 2 order by newid()select * from b inner join g on b.id = g.id and b.depId<>a.depIddrop table b
drop table g
drop table t
最佳的匹配是部门性别都不同，中等的匹配是部门不同性别相同，
最差的匹配是部门性别都相同，最后是没有找到匹配的。
create table #t(dept_id int,[name] nvarchar(10),sex int)
insert into #t
select 1,'张三',1 union all
select 1,'王芳',2 union all
select 1,'李丽',2 union all
select 2,'王五',2 union all
select 2,'赵玲',2 union all
select 2,'康正',1 union all
select 2,'康康',1
order by 2,1,3select * into #t_bak from #t
declare @dept_id int,@name nvarchar(10),@sex varchar(10),@name2 nvarchar(10),@id int
create table #t2(id int,name1 nvarchar(10),name2 nvarchar(10))
select @id = 0DECLARE Cursor_t CURSOR FOR
select * from #t
OPEN Cursor_t
FETCH NEXT FROM Cursor_t into @dept_id,@name,@sex
WHILE @@FETCH_STATUS = 0
BEGIN
set @id = @id + 1
--删除该条记录
delete #t
where dept_id = @dept_id and name = @name and sex = @sex
--查找匹配记录，部门、性别都不同
select top 1 @name2 = name
from #t
where dept_id <> @dept_id and name <> @name and sex <> @sex
order by dept_id,name if (@name2 is not null)
begin
delete #t--删除匹配记录
where name = @name2
--插入匹配表
insert into #t2
select @id,@name,@name2
end
else
begin
select top 1 @name2 = name--查找匹配记录，部门不同
from #t
where dept_id <> @dept_id and name <> @name
order by dept_id,name
if (@name2 is not null)
begin
delete #t--删除匹配记录
where name = @name2
--插入匹配表
insert into #t2
select @id,@name,@name2
end
else
begin
select top 1 @name2 = name--查找匹配记录
from #t
where name <> @name
order by dept_id,name
delete #t--删除匹配记录
where name = @name2
--插入匹配表
insert into #t2
select @id,@name,@name2
end
end
set @name2 = null
FETCH NEXT FROM Cursor_t into @dept_id,@name,@sex
END
CLOSE Cursor_t
DEALLOCATE Cursor_t--男左女右
update _t2
set _t2.name1 = _t2.name2,_t2.name2 = _t2.name1
from #t2 _t2
join #t_bak _a on _a.name = _t2.name1
join #t_bak _b on _b.name = _t2.name2
where _a.sex = 2 and _b.sex = 1select * from #t2drop table #t,#t2,#t_bakid name1 name2
----------- ---------- ----------
1 康康 李丽
2 康正 王芳
3 张三 王五
4 赵玲 NULL