create table t(depid int,[name] nvarchar(10),sex int)
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,'康铮',2
select 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.depId
create table t(id int,[name] nvarchar(10),sex int)
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,'康铮',2
select 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) b
select b.*,g.组合2 from b inner join g on b.id = g.id
楼上两位好像都不对吧。
最佳的匹配是部门性别都不同,中等的匹配是部门不同性别相同,
最差的匹配是部门性别都相同,最后是没有找到匹配的。
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,3
DECLARE 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 = 1