从列段中提取。。。。。

[leelin2010]

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 行受影响)
*/

[王向飞]

If not object_id('tempdb.dbo.#tb') is null
Drop table #tb
If not object_id('tempdb.dbo.#temp') is null
Drop table #temp
Go
Create table #temp([Num] int,[Count2] int,[Code] varchar(10))
Create table #tb([Num] int,[Count2] int,[Code] varchar(10))
--------------------------------------------------------------------
/*以上为构建临时表*/
--------------------------------------------------------------------
Insert #tb
--Select 9212636,200,'100202' union all
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
--------------------------------------------------------------------
/*以上为准备测试用数据*/
--------------------------------------------------------------------
declare @num int
declare @Count2 int
declare @Code varchar(max)
declare @start int --后四位尾数的前两位
declare @end int --后四位尾数的后两位
--------------------------------------------------------------------
/*以上为变量声明*/
--------------------------------------------------------------------
declare cur cursor
for
select Num,Count2,Code from #tb
where Num > 999

open cur 
fetch next from cur into @num,@Count2,@Code
while (@@fetch_status =0)
begin 
select @start = convert(int,substring(convert(varchar,@num),len(@num)-3,2))
select @end = convert(int,substring(convert(varchar,@num),len(@num)-1,2))
while @start <= @end
begin
insert into #temp
select @start,@Count2,@Code
set @start = @start +1
end
fetch next from cur into @num,@Count2,@Code
end
close cur 
deallocate cur

--------------------------------------------------------------------
/*以上为重新构造数据*/
--------------------------------------------------------------------
select * from #temp
union all
select * from #tb where Num <= 999 

PS 居然惊奇的找到一个跟你的问题一摸一样的提问。


链接

[叶子]

如果下面有23了，上面再分出来后是覆盖，还是重置？

[pt1314917]

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 b.number,count2,code from
(select minnum=case when len(num)>4 then left(right(num,4),2) else num end,
maxnum=case when len(num)>4 then right(right(num,4),2) else num end,count2,code from tb)a,master..spt_values b
where b.type='p' and b.number between a.minnum and a.maxnum


--结果：
number      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

[永生天地]

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 
a.num1+b.number num,
a.count2,
a.code
 from (
	select 
	num1=case when len(cast(num as varchar))>3 then left(right(cast(num as varchar),4),2) else num end,
	num2=case when len(cast(num as varchar))>3 then right(cast(num as varchar),2) else num end,
	count2,code
	from tb
) a ,master..spt_values b 
where b.type='p' 
and b.number<=num2-num1

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 行受影响)

[leelin2010]

[Quote=引用 2 楼 maco_wang 的回复:]
如果下面有23了，上面再分出来后是覆盖，还是重置？
[/Quote]
问得好，分出来的当然覆盖。。。。。