条码BarCode分散装条码和整箱条码,现在有2张条码表分别为bc1(里面既有整箱条码又有散装条码),bc2(里面只有散装条码)如下: bc1中 Barcode bc2中 Barcode
1 1
2 2
3 4
4 5
5
6
更具上面的例子说明3和6是整箱条码.
现在就要求判断bc1中哪个是整箱条码并把bc1中是整箱条码的抽出来,然后从这个抽出来的位置往上都是属于这个整箱条码的散装条码
例如上面例子说的3和6是整箱条码,那么3往上的1和2就是属于整箱条码3的散装条码.
最后查询后的结果就变成 Barcode
1 3
2 3
4 6
5 6
1 1
2 2
3 4
4 5
5
6
更具上面的例子说明3和6是整箱条码.
现在就要求判断bc1中哪个是整箱条码并把bc1中是整箱条码的抽出来,然后从这个抽出来的位置往上都是属于这个整箱条码的散装条码
例如上面例子说的3和6是整箱条码,那么3往上的1和2就是属于整箱条码3的散装条码.
最后查询后的结果就变成 Barcode
1 3
2 3
4 6
5 6
以下是你不需要的游标做的.
create table bc1(Barcode int)
create table bc2(Barcode int)
insert into bc1 values(1)
insert into bc1 values(2)
insert into bc1 values(3)
insert into bc1 values(4)
insert into bc1 values(5)
insert into bc1 values(6)
insert into bc2 values(1)
insert into bc2 values(2)
insert into bc2 values(4)
insert into bc2 values(5)
create table bc3(Barcode1 int , Barcode2 int)
godeclare @Barcode int;
set @Barcode = 0declare cur cursor fast_forward for
select Barcode from bc1;
open cur;
fetch next from cur into @Barcode;
while @@fetch_status=0
begin
if exists(select 1 from bc2 where Barcode = @Barcode)
insert into bc3(Barcode2) select @Barcode
else
update bc3 set Barcode1 = @Barcode where Barcode1 is null
fetch next from cur into @Barcode;
end
close cur;
deallocate cur;select * from bc3drop table bc1 , bc2 , bc3/*
Barcode1 Barcode2
----------- -----------
3 1
3 2
6 4
6 5(所影响的行数为 4 行)
*/
as
begin
select *, (select min(barcode)
from bc1 a
where a.barcode > b.barcode and a.barcode not in (select barcode
from bc2)
) as barcode1
from bc2 b
end
create table bc2(Barcode int)
insert into bc1 values(1)
insert into bc1 values(2)
insert into bc1 values(3)
insert into bc1 values(4)
insert into bc1 values(5)
insert into bc1 values(6)
insert into bc2 values(1)
insert into bc2 values(2)
insert into bc2 values(4)
insert into bc2 values(5)
go
select id = identity(int,1,1) ,Barcode Barcode1, Barcode2 = 0 into bc3 from bc1
go
create proc my_proc
as
begin
declare @i as int
set @i = 1
declare @cnt as int
select @cnt = count(1) from bc3
declare @Barcode as int
set @Barcode = 0
while @i <= @cnt
begin
select @Barcode = Barcode1 from bc3 where id = @i
if not exists(select 1 from bc2 where Barcode = @Barcode)
begin
update bc3 set Barcode2 = @Barcode where id < @i and Barcode2 = 0
update bc3 set Barcode2 = -1 where id = @i
end
set @i = @i + 1
end
end
goexec my_procdelete from bc3 where Barcode2 = -1
select Barcode1 , Barcode2 from bc3drop table bc1, bc2 , bc3drop proc my_proc/*
Barcode1 Barcode2
----------- -----------
1 3
2 3
4 6
5 6(所影响的行数为 4 行)
*/