高分求高手: 生成多行序列号,该如何解决

高分求高手: 生成多行序列号
一 、实现的功能要求: 生成多行字符号A-01-001-1到F-99-999-3之间所有的字符串.
  格式如:1位字母-2位数字-3位数字-1位数字  
  说明: 1、1位字母为[A-F]之间 
  2、2位数字[01-99]
  3、3位数字[001-999]
  4、1位数字[1-3]

二、SQL查询时效果如下:
  A-01-001-1 
  A-01-001-2
  A-01-001-3
  A-01-002-1 
  A-01-002-2
  A-01-002-3
  .........
  F-99-999-1
  F-99-999-2
  F-99-999-3



[解决办法]

SQL code

declare @ss varchar(20),@se varchar(100)select @ss='A-01-001-1',@se='F-99-999-3';with sn as(select top 1000 id=row_number() over(order by getdate()) from sys.objects,sys.columns)select s1+'-'+s2+'-'+s3+'-'+s4from(select char(ascii(left(@ss,1))+id-1) as s1 from snwhere ascii(left(@ss,1))+id-1<=ascii(left(@se,1))) t1,(select right('00'+ltrim(Parsename(replace(@ss,'-','.'),3)+id-1),2) as s2 from snwhere Parsename(replace(@ss,'-','.'),3)+id-1<=Parsename(replace(@se,'-','.'),3)) t2,(select right('000'+ltrim(Parsename(replace(@ss,'-','.'),2)+id-1),3) as s3 from snwhere Parsename(replace(@ss,'-','.'),2)+id-1<=Parsename(replace(@se,'-','.'),2)) t3,(select ltrim(right(@ss,charindex('-',reverse(@ss))-1)+id-1) s4 from snwhere right(@ss,charindex('-',reverse(@ss))-1)+id-1<=right(@se,charindex('-',reverse(@ss))-1)) t4
[解决办法]
SQL code
create table a (a_code char(1))insert aselect 'A' UNION SELECT 'B' UNION SELECT 'C' UNIONSELECT 'D' UNIONSELECT 'E' UNIONSELECT 'F'create table b (b_code char(2))insert bselect convert(varchar(2),right((101+number),2)) from master.dbo.spt_valueswhere type='P' and number <99create table c (c_code char(3))insert cselect convert(varchar(3),right((1001+number),3)) from master.dbo.spt_valueswhere type='P' and number <999create table d (id tinyint)insert dselect number+1 from master.dbo.spt_valueswhere type='P' and number <3select a.a_code+'-'+b.b_code+'-'+c.c_code+'-'+convert(varchar(20),d.id) from a cross join b cross join c cross join dorder by a.a_code,b.b_code,c.c_code,d.id/*(无列名)A-01-001-1A-01-001-2A-01-001-3A-01-002-1A-01-002-2A-01-002-3A-01-003-1A-01-003-2A-01-003-3A-01-004-1.........*/
[解决办法]
C# code
--A-99-999-3select Code INTO #T0 from(    select 'A' as Code UNION     SELECT 'B' as Code UNION     SELECT 'C' as Code UNION    SELECT 'D' as Code UNION    SELECT 'E' as Code UNION    SELECT 'F' as Code)aselect top 99  ROW_NUMBER() OVER (order by number)as RowNumber INTO #T1 from master..spt_values select top 999  ROW_NUMBER() OVER (order by number)as RowNumber INTO #T2 from master..spt_values select top 3  ROW_NUMBER() OVER (order by number)as RowNumber INTO #T3 from master..spt_values select convert(varchar(20),#T0.Code)+'-'+convert(varchar(2),right((101+#T1.RowNumber-1),2))+'-'+convert(varchar(3),right((1001+#T2.RowNumber-1),3))+'-'+convert(varchar(20),#T3.RowNumber)from #T0,#T1,#T2,#T3 drop table #T0drop table #T1drop table #T2drop table #T3