求一货位自动分配SQL算法

Question

select a.position_key,b.rfid_key
from
(
select row_number() over(order by getdate()) idx,position_key
from 
(
select position_key=1,position_name=N'货位1',sort_no=1,is_dist=0,rfid_key=-1
union select position_key=2,position_name=N'货位2',sort_no=2,is_dist=1,rfid_key=1
union select position_key=3,position_name=N'货位3',sort_no=3,is_dist=0,rfid_key=-1
union select position_key=4,position_name=N'货位4',sort_no=4,is_dist=0,rfid_key=-1
union select position_key=5,position_name=N'货位5',sort_no=5,is_dist=1,rfid_key=2
union select position_key=6,position_name=N'货位6',sort_no=6,is_dist=0,rfid_key=-1
union select position_key=7,position_name=N'货位7',sort_no=7,is_dist=0,rfid_key=-1 
) a
where is_dist=0
) a
inner join
(
select row_number() over(order by getdate()) idx,rfid_key from
(
select rfid_key=3
union select rfid_key=4
union select rfid_key=5 
) b
) b
on a.idx=b.idx
/*
1 3
3 4
4 5
*/

Answer 1

create table tb1(position_key int,position_name varchar(10),sort_no int,is_dist int,rfid_key int)
insert into tb1 values(1 ,           '货位1' ,          1 ,          0  ,         -1)
insert into tb1 values(2 ,           '货位2' ,          2 ,          1  ,         1)
insert into tb1 values(3 ,           '货位3' ,          3 ,          0  ,         -1)
insert into tb1 values(4 ,           '货位4' ,          4 ,          0  ,         -1)
insert into tb1 values(5 ,           '货位5' ,          5 ,          1  ,         2)
insert into tb1 values(6 ,           '货位6' ,          6 ,          0  ,         -1)
insert into tb1 values(7 ,           '货位7' ,          7 ,          0  ,         -1)
create table tb2 (rfid_key int)
insert into tb2 values(3)
insert into tb2 values(4)
insert into tb2 values(5)select m.position_key,n.rfid_key from
(select t.* , px = (select count(1) from tb1 where is_dist = 0 and position_key < t.position_key ) + 1 from tb1 t where is_dist = 0) m,
(select t.* , px = (select count(1) from tb2 where rfid_key < t.rfid_key) + 1 from tb2 t) n
where m.px = n.pxdrop table tb1,tb2/*
position_key rfid_key    
------------ ----------- 
1            3
3            4
4            5
*/

Answer 2

select *,id=identity(int,1,1) into #t1
from (
select position_key=1,position_name=N'货位1',sort_no=1,is_dist=0,rfid_key=-1
union select position_key=2,position_name=N'货位2',sort_no=2,is_dist=1,rfid_key=1
union select position_key=3,position_name=N'货位3',sort_no=3,is_dist=0,rfid_key=-1
union select position_key=4,position_name=N'货位4',sort_no=4,is_dist=0,rfid_key=-1
union select position_key=5,position_name=N'货位5',sort_no=5,is_dist=1,rfid_key=2
union select position_key=6,position_name=N'货位6',sort_no=6,is_dist=0,rfid_key=-1
union select position_key=7,position_name=N'货位7',sort_no=7,is_dist=0,rfid_key=-1 
) t
where is_dist=0
order by position_keyselect *,id=identity(int,1,1) into #t2
from (
select rfid_key=3
union select 4
union select 5 
) tselect position_key,t2.rfid_key
from #t1 as t1 join #t2 as t2
on t1.id=t2.iddrop table #t1
drop table #t2
/*
position_key rfid_key    
------------ ----------- 
1            3
3            4
4            5
*/

Answer 3

order by getdate()
?
建议改成select a.position_key,b.rfid_key
    from
    (
        select row_number() over(order by position_key) idx,position_key
            from 
            (
                    select position_key=1,position_name=N'货位1',sort_no=1,is_dist=0,rfid_key=-1
                    union select position_key=2,position_name=N'货位2',sort_no=2,is_dist=1,rfid_key=1
                    union select position_key=3,position_name=N'货位3',sort_no=3,is_dist=0,rfid_key=-1
                    union select position_key=4,position_name=N'货位4',sort_no=4,is_dist=0,rfid_key=-1
                    union select position_key=5,position_name=N'货位5',sort_no=5,is_dist=1,rfid_key=2
                    union select position_key=6,position_name=N'货位6',sort_no=6,is_dist=0,rfid_key=-1
                    union select position_key=7,position_name=N'货位7',sort_no=7,is_dist=0,rfid_key=-1 
            ) a
            where is_dist=0
    ) a
inner join
    (
        select row_number() over(order by rfid_key) idx,rfid_key from
        (
            select rfid_key=3
            union select rfid_key=4
            union select rfid_key=5 
        ) b
    ) b
    on a.idx=b.idx

Answer 4

货位表：
select * into #position 
from (
select position_key=1,position_name=N'货位1',sort_no=1,is_dist=0,rfid_key=-1,height=150
union select position_key=2,position_name=N'货位2',sort_no=2,is_dist=1,rfid_key=1,height=100
union select position_key=3,position_name=N'货位3',sort_no=3,is_dist=0,rfid_key=-1,height=180
union select position_key=4,position_name=N'货位4',sort_no=4,is_dist=0,rfid_key=-1,height=100
union select position_key=5,position_name=N'货位5',sort_no=5,is_dist=1,rfid_key=2,height=100
union select position_key=6,position_name=N'货位6',sort_no=6,is_dist=0,rfid_key=-1,height=260
union select position_key=7,position_name=N'货位7',sort_no=7,is_dist=0,rfid_key=-1,height=100
) tab等分配货物：
select * into #rfid
from (
select rfid_key=3,height=100
union select rfid_key=4,height=160
union select rfid_key=5,height=150
) tab说明：我有一个货位表，里面标识哪些货位已经分配数据。如is_dist=1则为已分配；0为未分配；
现在我有三条记录，想自动按货位的顺序分配（sort_no),并且还要满足货物的高度要小于货位的高度。
目的结果是：
position_key,rfid_key
1               3
3               4
6               5
请问如何用Sql写出这个算法，谢谢！

Answer 5

declare @a table (position_key int,rfid_key int);
while @@rowcount >0
begin
        insert into @a select top 1 a.position_key, b.rfid_key from #position a,#rfid b 
        where a.is_dist=0 and a.height >= b.height
        and not exists (select * from @a c where a.position_key = c.position_key)
        and not exists (select * from @a d where b.rfid_key = d.rfid_key)
        order by a.position_key, b.rfid_key
end;
select * from @a;/*结果
position_key       rfid_key
1               3
3               4
6               5
*/

Answer 6

select * into #position 
from ( 
select position_key=1,position_name=N'货位1',sort_no=1,is_dist=0,rfid_key=-1,height=150 
union select position_key=2,position_name=N'货位2',sort_no=2,is_dist=1,rfid_key=1,height=100 
union select position_key=3,position_name=N'货位3',sort_no=3,is_dist=0,rfid_key=-1,height=180 
union select position_key=4,position_name=N'货位4',sort_no=4,is_dist=0,rfid_key=-1,height=100 
union select position_key=5,position_name=N'货位5',sort_no=5,is_dist=1,rfid_key=2,height=100 
union select position_key=6,position_name=N'货位6',sort_no=6,is_dist=0,rfid_key=-1,height=260 
union select position_key=7,position_name=N'货位7',sort_no=7,is_dist=0,rfid_key=-1,height=100 
) tab select * into #rfid 
from ( 
select rfid_key=3,height=100 
union select rfid_key=4,height=160 
union select rfid_key=5,height=150 
) tab--将所有可能的组合插入#中
select b.rfid_key,a.position_key,a.sort_no
into #
from #position a , #rfid b
where a.is_dist=0 and a.height>=b.height
order by b.rfid_key,a.sort_no
--将满足条件的分配结果依次插入#1中
select top 1 * into #1 from #
while @@rowcount>0
insert #1
select top 1 *
from #
where rfid_key>(select max(rfid_key) from #1)
and position_key not in (select position_key from #1)
order by rfid_key,sort_no
--查看结果
select position_key,rfid_key from #1 order by position_key
/*
position_key rfid_key
------------ -----------
1            3
3            4
6            5(3 行受影响)
*/
drop table #
drop table #1

Answer 7

select *,id=identity(int,1,1) into #t1
from (
select position_key=1,position_name=N'?位1',sort_no=1,is_dist=0,rfid_key=-1,height=150
union select position_key=2,position_name=N'?位2',sort_no=2,is_dist=1,rfid_key=1,height=100
union select position_key=3,position_name=N'?位3',sort_no=3,is_dist=0,rfid_key=-1,height=180
union select position_key=4,position_name=N'?位4',sort_no=4,is_dist=0,rfid_key=-1,height=100
union select position_key=5,position_name=N'?位5',sort_no=5,is_dist=1,rfid_key=2,height=100
union select position_key=6,position_name=N'?位6',sort_no=6,is_dist=0,rfid_key=-1,height=260
union select position_key=7,position_name=N'?位7',sort_no=7,is_dist=0,rfid_key=-1,height=100 
) t
where is_dist=0
order by position_keyselect *,id=identity(int,1,1) into #t2
from (
select rfid_key=3,height=100
union select rfid_key=4,height=160
union select rfid_key=5,height=150 
) tcreate table #t3(position_key int,rfid_key int)declare @rowcount int,@id int
set @rowcount=(select count(*) from #t2)
set @id=1while @rowcount>0
begin   
  insert #t3
  select t2. *
  from (
  select  position_key=(select top 1 t1.position_key 
                                    from #t1  as t1 left join #t3 as t3 on t1.position_key=t3.position_key
                                    where height>t2.height and  t3.position_key is null order by t1.position_key),
            rfid_key
  from #t2 as t2 
  where id=@id
  ) t2
  set @rowcount=@rowcount-1
  set @id=@id+1
endselect * from #t3 order by position_keydrop table #t1  
drop table #t2
drop table #t3
/*
position_key rfid_key    
------------ ----------- 
1            3
3            4
6            5
*/

Answer 8

declare @a table (position_key int,rfid_key int);insert into @a select top 1 a.position_key, b.rfid_key from #position a,#rfid b 
where a.is_dist=0 and a.height >= b.height
order by a.position_key, b.rfid_keywhile @@rowcount >0
begin
        insert into @a select top 1 a.position_key, b.rfid_key from #position a,#rfid b 
        where a.is_dist=0 and a.height >= b.height
        and not exists (select * from @a c where a.position_key = c.position_key)
        and not exists (select * from @a d where b.rfid_key = d.rfid_key)
        order by a.position_key, b.rfid_key
end;
select * from @a;/*结果
position_key       rfid_key
1                  3
3                  4
6                  5
*/