高手们:
现在有一个表PzhA中有一段票号:起始是:2001,截止号是:2010,数量为10份,请问如何把该段票证号码拆分成:pzh1:
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010并插入到一个新的表格PzhB中去!!谢谢!!
现在有一个表PzhA中有一段票号:起始是:2001,截止号是:2010,数量为10份,请问如何把该段票证号码拆分成:pzh1:
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010并插入到一个新的表格PzhB中去!!谢谢!!
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (起号 int,止号 int,数量 int,行号 int)
insert into #T
select 2001,2010,10,1
;
with cte as
(
select 起号,行号,止号 from #T
union all
select 起号+行号,行号,止号 from cte where 起号+行号<=止号
)
select 起号 as ID from cte/*
ID
-----------
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010(10 行受影响)*/
/*
功能:实现split功能的函数
*/create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as begin
declare @i intset @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
endif @inputstr <> '\'
insert @temp values(@inputstr)return
end
go--调用declare @s varchar(1000)set @s='1,2,3,4,5,6,7,8,55'select * from dbo.fn_split(@s,',')drop function dbo.fn_split
--语句
create table #t(ID int)
declare @IDvarchar varchar(1000)
set @IDvarchar='2001$2002$2003'set @IDvarchar='insert into #t(ID) select '+replace(@IDvarchar,'$',' union all select ')
exec(@IDvarchar)select * from #t
drop table t
--result
ID
-----------
2001
2002
2003
select * from num ----num 为派生表 列为 id 从 1-100000 没个自增加1select * into #t from
(select bid=2001,eid=2010 ,num=10,row=1 )xselect num.id from #t a
inner join num
on num.id between a.bid and a.eid
returns @temp table(bill bigint)
as
begin
--declare @i bigint
--set @i=start
while @start<=@stop
begin
insert into @temp values (@start)
set @start=@start+1
end
return
end
--调用
select * from f_billCreator(2001,2010)
/*
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
*/
insert @T select 2001,2010;with T as (select 1 as ID union all select 1)
,T1 as (select a.ID from t a,t b)
,T2 as (select a.ID from t1 a,t1 b)
,Num as (select ID=row_number()over(Order by ID)-1 from T2)
select
b.StartNum+a.ID as StartNum
from
Num a,@T b
where
b.StartNum+a.ID!>b.EndNum
(1 個資料列受到影響)
StartNum
--------------------
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010(10 個資料列受到影響)
drop function f_creator
go
create function f_creator(@start varchar(10),@how int)
returns @temp table(bill varchar(10))
as
begin
declare @s bigint
set @s=@start
declare @i int,@l int
set @i=0
set @l=len(@start)
while @i<@how
begin
insert into @temp values (right('00000000'+upper(@s),@l))
set @s=@s+1
set @i=@i+1
end
return
end
go
--调用
select * from f_creator('002001',10)
/*
002001
002002
002003
002004
002005
002006
002007
002008
002009
002010
*/
看来2005是增加了很多功能,with语句很有用!