高分求高手: 生成多行序列号,该如何解决
高分求高手: 生成多行序列号
一 、实现的功能要求: 生成多行字符号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
------解决方案--------------------
一 、实现的功能要求: 生成多行字符号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+'-'+s4 from ( select char(ascii(left(@ss,1))+id-1) as s1 from sn where 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 sn where 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 sn where Parsename(replace(@ss,'-','.'),2)+id-1<=Parsename(replace(@se,'-','.'),2) ) t3, ( select ltrim(right(@ss,charindex('-',reverse(@ss))-1)+id-1) s4 from sn where 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 a select 'A' UNION SELECT 'B' UNION SELECT 'C' UNION SELECT 'D' UNION SELECT 'E' UNION SELECT 'F' create table b (b_code char(2)) insert b select convert(varchar(2),right((101+number),2)) from master.dbo.spt_values where type='P' and number <99 create table c (c_code char(3)) insert c select convert(varchar(3),right((1001+number),3)) from master.dbo.spt_values where type='P' and number <999 create table d (id tinyint) insert d select number+1 from master.dbo.spt_values where type='P' and number <3 select a.a_code+'-'+b.b_code+'-'+c.c_code+'-'+convert(varchar(20),d.id) from a cross join b cross join c cross join d order by a.a_code,b.b_code,c.c_code,d.id /* (无列名) A-01-001-1 A-01-001-2 A-01-001-3 A-01-002-1 A-01-002-2 A-01-002-3 A-01-003-1 A-01-003-2 A-01-003-3 A-01-004-1 ... ... ...*/
------解决方案--------------------
- C# code
--A-99-999-3 select 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 )a select 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 #T0 drop table #T1 drop table #T2 drop table #T3