我有一个数据表,结构为BMDM,XM,XBM,即 部门代码,姓名,性别代码我里面的数据是这样的:
1.每个部门的人数不一定相等
2.所有数据男女数不一定相等
要求查询结果:
1.尽量男女搭配,男或女多了的,则同一性别排在一起;
2.尽量将同一部门的分开,比如A部门尽量不要同A部门排列在一起;
3.相同情况下,第1条优先输出格式为:
某某(男)\某某(女)谢谢了,急用.
1.每个部门的人数不一定相等
2.所有数据男女数不一定相等
要求查询结果:
1.尽量男女搭配,男或女多了的,则同一性别排在一起;
2.尽量将同一部门的分开,比如A部门尽量不要同A部门排列在一起;
3.相同情况下,第1条优先输出格式为:
某某(男)\某某(女)谢谢了,急用.
from
(select who from person where sex = '男' and alia = 'Me') as a,
(select who from person where sex = '女' and type = 'beauty') as b:)
union all select 1, 'B男', 1
union all select 1, 'C女', 0
union all select 1, 'D女', 0
union all select 1, 'E女', 0
union all select 2, 'F男', 1
union all select 2, 'G男', 1
union all select 2, 'H男', 1
union all select 2, 'I女', 0
union all select 2, 'J女', 0select nId=identity(int,1,1), XM into #M from
(
select top 50 percent XM from tb order by XBM desc, BMDM desc
) tbselect nId=identity(int,1,1), XM into #F from
(
select top 50 percent XM from tb order by XBM, BMDM
) tbselect nId=coalesce(m.nId, f.nId), 配对=m.XM+'(男)\'+f.XM+'(女)'
from #M m full join #F f on m.nId=f.nId /*
nId 配对
----------- -----------------------------------------
1 F男(男)\C女(女)
2 G男(男)\D女(女)
3 H男(男)\E女(女)
4 A男(男)\I女(女)
5 B男(男)\J女(女)(5 row(s) affected)
*/drop table tb, #M, #F
--今天先到这里
declare @t table (
BMDM varchar(10),
XM varchar(10),
XBM varchar(10)
)
insert @t select
'A','1','M'
union all select
'B','2','M'
union all select
'C','3','M'
union all select
'A','4','F'
union all select
'A','5','M'
union all select
'B','6','F'
union all select
'C','7','M'
union all select
'C','8','F'
union all select
'C','9','M'
union all select
'A','10','F'
union all select
'B','11','M'declare @r table (
XM varchar(10),
XBM varchar(10),
XM1 varchar(10),
XBM1 varchar(10)
)while exists (select 1 from @t a,@t b
where a.xm<b.xm
and a.XBM<>b.XBM
and a.BMDM<>b.BMDM
)
begin
insert @r
select top 1 a.XM,a.XBM,B.XM,B.XBM from @t a,@t b
where a.xm<b.xm
and a.XBM<>b.XBM
and a.BMDM<>b.BMDM
order by newid()delete t
from @t t,@r r
where t.XM=r.XM
or t.XM=r.XM1
endwhile exists (select 1 from @t a,@t b
where a.xm<b.xm
and a.XBM<>b.XBM
)
begin
insert @r
select top 1 a.XM,a.XBM,B.XM,B.XBM from @t a,@t b
where a.xm<b.xm
and a.XBM<>b.XBM
order by newid()delete t
from @t t,@r r
where t.XM=r.XM
or t.XM=r.XM1
end
while exists (select 1 from @t a,@t b
where a.xm<b.xm
)
begin
insert @r
select top 1 a.XM,a.XBM,B.XM,B.XBM from @t a,@t b
where a.xm<b.xm
order by newid()delete t
from @t t,@r r
where t.XM=r.XM
or t.XM=r.XM1
endselect XM+'('+XBM+')\('+XM1+'('+XBM1+')' from @r --配对结果
select * from @t --可能有倒霉蛋
create table tb(BMDM int, XM varchar(16), XBM int)insert tb select 1, 'A男', 1
union all select 1, 'B男', 1
union all select 1, 'C女', 0
union all select 1, 'D女', 0
union all select 1, 'E女', 0
union all select 2, 'F男', 1
union all select 2, 'G男', 1
union all select 2, 'H男', 1
union all select 2, 'I女', 0
union all select 2, 'J女', 0select nId=identity(int,1,1), XM, XB into #M from
(
select top 50 percent XM, XB=case XBM when 1 then '男' else '女' end
from tb order by XBM desc, BMDM desc
) tbselect nId=identity(int,1,1), XM, XB into #F from
(
select top 50 percent XM, XB=case XBM when 1 then '男' else '女' end
from tb order by XBM, BMDM
) tbselect nId=coalesce(m.nId, f.nId), 配对=isnull(m.XM+'('+m.XB+')','')+'|'+isnull(f.XM+'('+f.XB+')','')
from #M m full join #F f on m.nId=f.nId /*
nId 配对
----------- -----------------------------------------
1 F男(男)|C女(女)
2 G男(男)|D女(女)
3 H男(男)|E女(女)
4 A男(男)|I女(女)
5 B男(男)|J女(女)(5 row(s) affected)
*/drop table tb, #M, #F
BMDM varchar(10),
XM varchar(10),
XBM varchar(10)
)
insert @t select
'A','1','M'
union all select
'B','2','M'
union all select
'C','3','M'
union all select
'A','4','F'
union all select
'A','5','M'
union all select
'B','6','F'
union all select
'C','7','M'
union all select
'C','8','F'
union all select
'C','9','M'
union all select
'A','10','F'
union all select
'B','11','M'
select a.bmdm as abmdm,a.xm+a.xbm+'/'+b.xm+b.xbm as tmp ,b.bmdm as bbmdm into #
from @t a
left join @t b on a.bmdm <> b.bmdm and a.xbm <> b.xbm
where a.xbm = 'F' and b.xbm = 'M'
select * into #1
from # c
where not exists(select 1
from #
where substring(tmp,1,charindex('/',tmp) - 1) = substring(c.tmp,1,charindex('/',c.tmp) - 1)
and substring(tmp,charindex('/',tmp) + 1,10) > substring(c.tmp,charindex('/',c.tmp) + 1,10))select *
from #1 d
where not exists(select 1
from #1
where substring(tmp,1,charindex('/',tmp) - 1) > substring(d.tmp,1,charindex('/',d.tmp) - 1)
and substring(tmp,charindex('/',tmp) + 1,10) = substring(d.tmp,charindex('/',d.tmp) + 1,10))drop table #,#1
部门数、每个部门的人数以及男、女数都为不确定,任何情况都存在。要求还是那样,男、女尽量配对,同一部门尽量不放在一起(除非确实没有办法了)。还有就是随机。
您的好象不对,运行结果不正确。