select *,id=identity(int,1,1) into #t1
from (
select position_key=1,group_id='g1',sort_no=1,position_count=3
union select position_key=2,group_id='g2',sort_no=2,position_count=5
union select position_key=3,group_id='g3',sort_no=3,position_count=7
union select position_key=4,group_id='',sort_no=4,position_count=10
) t
select *,id=identity(int,1,1) into #t2
from (
select rfid_key=1,group_id='g1'
union select rfid_key=2,group_id='g1'
union select rfid_key=3,group_id='g1'
union select rfid_key=4,group_id='g1'
union select rfid_key=5,group_id='g2'
union select rfid_key=6,group_id='g2'
union select rfid_key=7,group_id='g5'
) t
create table #t3(rfid_key int,position_key int)declare @rowcount int,@id int,@group_id varchar(2),@position_key int,@rfid_key int
set @rowcount=(select count(*) from #t2)
set @id=1while @rowcount>0
begin
set @group_id=(select group_id from #t2 where id=@id)
set @rfid_key=(select rfid_key from #t2 where id=@id) update #t1 set group_id=@group_id,
position_count=a.position_count-1,
@position_key=a.position_key
from #t1 a join (select top 1 * from #t1 where (group_id=@group_id or group_id='') and position_count>0 order by id) b on a.id=b.id if @@rowcount>0
begin
insert #t3
select @rfid_key,@position_key
end
else
begin
insert #t3
select @rfid_key,null
end set @rowcount=@rowcount-1
set @id=@id+1
endselect * from #t3 order by rfid_keydrop table #t1
drop table #t2
drop table #t3
/*
rfid_key position_key
----------- ------------
1 1
2 1
3 1
4 4
5 2
6 2
7 NULL
*/
from (
select position_key=1,group_id='g1',sort_no=1,position_count=3
union select position_key=2,group_id='g2',sort_no=2,position_count=5
union select position_key=3,group_id='g3',sort_no=3,position_count=7
union select position_key=4,group_id='',sort_no=4,position_count=10
) t
select *,id=identity(int,1,1) into #t2
from (
select rfid_key=1,group_id='g1'
union select rfid_key=2,group_id='g1'
union select rfid_key=3,group_id='g1'
union select rfid_key=4,group_id='g1'
union select rfid_key=5,group_id='g2'
union select rfid_key=6,group_id='g2'
union select rfid_key=7,group_id='g5'
) t
create table #t3(rfid_key int,position_key int)declare @rowcount int,@id int,@group_id varchar(2),@position_key int,@rfid_key int
set @rowcount=(select count(*) from #t2)
set @id=1while @rowcount>0
begin
set @group_id=(select group_id from #t2 where id=@id)
set @rfid_key=(select rfid_key from #t2 where id=@id) update #t1 set group_id=@group_id,
position_count=a.position_count-1,
@position_key=a.position_key
from #t1 a join (select top 1 * from #t1 where (group_id=@group_id or group_id='') and position_count>0 order by id) b on a.id=b.id if @@rowcount>0
begin
insert #t3
select @rfid_key,@position_key
end
else
begin
insert #t3
select @rfid_key,null
end set @rowcount=@rowcount-1
set @id=@id+1
endselect * from #t3 order by rfid_keydrop table #t1
drop table #t2
drop table #t3
/*
rfid_key position_key
----------- ------------
1 1
2 1
3 1
4 4
5 2
6 2
7 NULL
*/
解决方案 »
- 查询出不同的然后计数。
- 如何从SQLserver2000中导出表到ORACLE(在线等,up有分)
- 我在查询分析器里运行exec('select * from '+@table)后不显示结果?
- 高手们,请教最实用的SQL的并发控制办法,
- 这个语句可以优化一下性能么?
- set @business_code='''TEXT_00000'',''TEXT_00001'',''TEXT_00002'',''TEXT_00003'''
- 是左外连接吗?
- 我写了一个.net的程序,运行一段时候后,sqlserver占cpu的资源就达到了92%以上
- select * from zh_ziliao where ziliaosign in (ggyy,wl,china)
- 无法打开用户默认数据库,登陆失败
- 2008年最后一问,sql安装SP4补丁的问题
- 两个数据库之间怎么实现数据交换
--if sql 2005:
select rfid_key,position_key=case
when a.rn>position_count
then (select position_key from #t1 where group_id='')
else b.position_key
end
from (select *,rn=row_number() over(partition by group_id order by id) from #t2) a
left join #t1 b
on a.group_id=b.group_id
/*
rfid_key position_key
----------- ------------
1 1
2 1
3 1
4 4
5 2
6 2
7 NULL(7 行受影响)
*/
when a.rn>position_count
then (select position_key from #t1 where group_id='')
else b.position_key
end
from (select *,rn=row_number() over(partition by group_id order by id) from #t2) a
left join #t1 b
on a.group_id=b.group_id