27,579
社区成员
发帖
与我相关
我的任务
分享
declare @i int,@s varchar(20)
set @i=26*26*26-1
set @s=''
while @i>=0
begin
set @s=char(ascii('A')+(@i % 26))+@s
set @i=@i/26
if @i=0 break
end
print @s
if len(@s)>1
set @s=char(ascii(substring(@s,1,1))-1)+right(@s,len(@s)-1)
print @s
;WITH cte AS
(
SELECT TOP(26)
colno = ROW_NUMBER() OVER(ORDER BY GETDATE()),
colname = CHAR(64 + ROW_NUMBER() OVER(ORDER BY GETDATE()))
FROM sys.columns a, sys.columns b
),
cte_map AS
(
SELECT colno, colname FROM cte
UNION ALL
SELECT TOP(230)
colno = ROW_NUMBER() OVER(ORDER BY a.colno, b.colno) + 26,
colname = a.colname + b.colname
FROM cte a, cte b
)
SELECT * FROM cte_map WHERE colno = 256 --范围:1-256
/*
colno colname
256 IV
*/
--不够继续+union all
with cte
as(select ROW_NUMBER() over (order by len(name),name) as id,name from
(select name from tb
union all
select a.name+b.name from tb a,tb b
union all
select a.name+b.name+c.name from tb a,tb b,tb c
union all
select a.name+b.name+c.name+d.name from tb a,tb b,tb c,tb d) a)
select top 2 * from cte where id<=400000
order by len(name) desc,name desc
id name
-------------------- ----------------------------------------
400000 VSRP
399999 VSRO
create table tb (num int identity(1,1),name varchar(10))
declare @a varchar(10)
set @a='A'
while @a<'Z'
begin
insert into tb values (@a)
set @a=CHAR(ASCII(''+@a+'')+1)
end
--select * from tb
--版本什么的好像不要,无论什么版本只要输入的最大@id不超过该版本支持的最大行数就可以
declare @id int
set @id=5
;with cte
as(select ROW_NUMBER() over (order by len(name),name) as id,name from
(select name from tb
union all
select a.name+b.name from tb a,tb b) a)
select name from cte where id<=@id
--其实就是简化为如下
;with cte
as(select ROW_NUMBER() over (order by len(name),name) as id,name from
(select name from tb
union all
select a.name+b.name from tb a,tb b) a)
select name from cte where id<=10000--随便自己输入
name
--------------------
A
B
C
D
E
-- 数值转换成Excel (完全版:包括2007版)
alter function col_rev(
@i_version varchar(20), -- Excel 版本号
@i_col int -- 字段号(数值表示)
)
returns varchar(30)
as
begin
declare @v_devide int;
declare @v_mod int;
declare @v_col int;
declare @str nvarchar(20);
set @v_col=@i_col;
set @str = '';
if @i_version='2003' and @v_col<=256 -- Excle 2003 最多256列
begin
set @str= (case when @v_col>=27 then char(ascii('@')+((@v_col-26-1)/26)%26+1)+char(ascii('@')+((@v_col-1)%26)+1)
else char(ascii('@')+((@v_col-1)%26)+1) end);
end
else if @i_version='2007' and @v_col<=16384 -- Excle 2007 最多16384列
begin
set @str= (case when @v_col>=703 then char(ascii('@')+((@v_col-703)/676)%26+1)+char(ascii('@')+((@v_col-26-1)/26)%26+1)+char(ascii('@')+((@v_col-1)%26)+1)
when @v_col>=27 and @v_col<=702 then char(ascii('@')+((@v_col-26-1)/26)%26+1)+char(ascii('@')+((@v_col-1)%26)+1)
else char(ascii('@')+((@v_col-1)%26)+1) end);
end
else
begin
set @str='输入有误,请检查!';
end
return @str;
end
------------------- 测试 ----------------------------------------
begin
declare @i int;
declare @str varchar(20);
set @i=1;
while @i<=16384
begin
select @str=dbo.col_rev('2007',@i);
print (@str);
set @i=@i+1;
end
end
-- 正确答案:
alter function col_rev(
@i_version int, -- Excel 版本号
@i_col int -- 字段号(数值表示)
)
returns varchar(20)
as
begin
declare @v_devide int;
declare @v_mod int;
declare @v_col int;
declare @str nvarchar(20);
set @v_col=@i_col;
set @str = '';
if @i_version=2003 and @v_col<=256 -- Excle 2003 最多256列
begin
if @v_col>26
begin
set @str= (CASE when (@v_col%26)=0 then char(ascii('A')+(@v_col/26)-2)+'Z'
ELSE char(ascii('A')+(@v_col/26)-1)+char(ascii('A')+(@v_col%26)-1) end);
end
else if @v_col=26
begin
set @str = 'Z';
end
else
begin
select @str=char(ascii('A')+(@v_col%26)-1);
end
end
return @str;
end
select '76' AS C1, dbo.col_rev(2003,76) as col UNION ALL
select '77' AS C1, dbo.col_rev(2003,77) as col UNION ALL
select '78' AS C1, dbo.col_rev(2003,78) as col UNION ALL
select '79' AS C1, dbo.col_rev(2003,79);
-- 修正一下:
alter function col_rev(
@i_version int, -- Excel 版本号
@i_col int -- 字段号(数值表示)
)
returns varchar(20)
as
begin
declare @v_devide int;
declare @v_mod int;
declare @v_col int;
declare @str nvarchar(20);
set @v_col=@i_col;
set @str = '';
if @i_version=2003 and @v_col<=256 -- Excle 2003 最多256列
begin
if @v_col>26
begin
set @str=char(ascii('A')+(@v_col/26)-1)+(case when @v_col%26<>0 then char(ascii('A')+(@v_col%26)-1) else 'Z' end);
end
else if @v_col=26
begin
set @str = 'Z';
end
else
begin
select @str=char(ascii('A')+(@v_col%26)-1);
end
end
return @str;
end
-- 这样修改一下嘛:
alter function col_rev(
@i_version int, -- Excel 版本号
@i_col int -- 字段号(数值表示)
)
returns varchar(20)
as
begin
declare @v_devide int;
declare @v_mod int;
declare @v_col int;
declare @str nvarchar(20);
set @v_col=@i_col;
set @str = '';
if @i_version=2003 and @v_col<=256 -- Excle 2003 最多256列
begin
if @v_col>26
begin
set @str=char(ascii('A')+(@v_col/26)-1)+(case when @v_col%26<>0 then char(ascii('A')+(@v_col%26)-1) else '' end);
end
else if @v_col=26
begin
set @str = 'Z';
end
else
begin
select @str=char(ascii('A')+(@v_col%26)-1);
end
end
return @str;
end
-- 先写一个2003版本的给你:
alter function col_rev(
@i_version int, -- Excel 版本号
@i_col int -- 字段号(数值表示)
)
returns varchar(20)
as
begin
declare @v_devide int;
declare @v_mod int;
declare @v_col int;
declare @str nvarchar(20);
set @v_col=@i_col;
set @str = '';
if @i_version=2003 and @v_col<=256 -- Excle 2003 最多256列
begin
if @v_col>26
begin
set @str=char(ascii('A')+(@v_col/26)-1)+char(ascii('A')+(@v_col%26)-1);
end
else if @v_col=26
begin
set @str = 'Z';
end
else
begin
select @str=char(ascii('A')+(@v_col%26)-1);
end
end
return @str;
end
select dbo.col_rev(2003,254) as col;