【原】一道SQL题(根据一个字段值的后四位的两两差，生成对应的数据行)

题目如下：
If not object_id('[tb]') is null
    Drop table [tb]
Go
Create table [tb]([Num] int,[Count2] int,[Code] varchar(10))
Insert tb
Select 92126,200,'100202' union all
Select 94752,100,'100202' union all
Select 19,200,'100201' union all
Select 20,100,'100201'
Go
--Select * from tb

要求结果:把表中的num的后四位作为新的num

比如2126就生成21-26的6个num

/*
num         Count2      code
----------- ----------- ----------
21          200         100202
22          200         100202
23          200         100202
24          200         100202
25          200         100202
26          200         100202
47          100         100202
48          100         100202
49          100         100202
50          100         100202
51          100         100202
52          100         100202
19          200         100201
20          100         100201

(14 行受影响)
*/

--方法1
declare @result table
(
    Num int,
    Count2 int,
    Code varchar(10)
);
declare @value as int,@count as int,@i as int;
declare @count2 as int,@code as varchar(10);
declare @prvvalue as int;

declare c cursor fast_forward for
    select
    LEFT(RIGHT(tb.Num,4),2) as [value],
    CAST(RIGHT(tb.Num,2) as int) - CAST(LEFT(RIGHT(tb.Num,4),2) as int) as [count],
    tb.Count2,
    tb.Code
    from tb;

open c

fetch next from c into @value,@count,@count2,@code;

select @i = 0,@prvvalue = @value;

while @@FETCH_STATUS = 0
begin
    if @prvvalue <> @value
        select @prvvalue = @value,@i = 0;

    while @i <= @count
    begin
        insert into @result values(@value,@count2,@code);
        set @value = @value + 1;
        set @i = @i + 1;
    end
    fetch next from c into @value,@count,@count2,@code;
end

close c;
deallocate c;

select *
from @result;

--方法2
select b.number as num,a.[Count2],a.code
from tb as a
join master..spt_values as b
on b.type='p'
and a.num > 90000
and b.number >= (a.num-90000) / 100
and b.number <= (a.num-90000) % 100
union all
select * from tb where num < 90000