--> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] create table [tb]([A] int) insert [tb] select 2 union all select 1 union all select 3 union all select 4 union all select 2 union all select 5 --函数 create function dbo.getcharacter(@id int) returns varchar(8000) as begin declare @ret varchar(8000) select @ret=isnull(@ret+',','')+code from (select char(number) as code from master..spt_values where type = 'P' and number >= 65 and number <= 65+@id-1 ) t return @ret end--查询 select A,dbo.getcharacter(A) as B from tb ----------------------- 2 A,B 1 A 3 A,B,C 4 A,B,C,D 2 A,B 5 A,B,C,D,E
--要小写就用 select A,lower(dbo.getcharacter(A)) as B from tb
不会超过26,我觉得最快的速度是穷举。select case colA when 1 then 'a' when 2 then 'a,b' when 3 then 'a,b,c' ... end from T
数字跟字母的关系,可以用char()函数,或者ascii()函数相互转换。 与其写一个合并字串的function, 不如直接穷举 case when 好了
超过26可以这样,aa,ab,ac,ad处理
if object_id('[tb]') is not null drop table [tb] create table [tb]([A] int) insert [tb] select 2 union all select 1 union all select 3 union all select 28 union all select 4 union all select 2 union all select 5 --函数 alter function dbo.getcharacter(@id int) returns varchar(8000) as begin declare @ret varchar(8000) select @ret=isnull(@ret+',','')+code from (select case when number>=90 then char(65*(number/90))+char(number-90+65) else char(number) end as code from master..spt_values where type = 'P' and number >= 65 and number <= 65+@id-1 ) t return @ret endselect dbo.getcharacter(a) from tb
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- A ,B A A ,B ,C A ,B ,C ,D ,E ,F ,G ,H ,I ,J ,K ,L ,M ,N ,O ,P ,Q ,R ,S ,T ,U ,V ,W ,X ,Y ,AA,AB,AC A ,B ,C ,D A ,B A ,B ,C ,D ,E (所影响的行数为 7 行)
if object_id('[tb]') is not null drop table [tb]
create table [tb]([A] int)
insert [tb]
select 2 union all
select 1 union all
select 3 union all
select 4 union all
select 2 union all
select 5
--函数
create function dbo.getcharacter(@id int)
returns varchar(8000)
as
begin
declare @ret varchar(8000) select @ret=isnull(@ret+',','')+code
from
(select char(number) as code
from master..spt_values
where type = 'P' and number >= 65 and number <= 65+@id-1
) t return @ret
end--查询
select A,dbo.getcharacter(A) as B
from tb
-----------------------
2 A,B
1 A
3 A,B,C
4 A,B,C,D
2 A,B
5 A,B,C,D,E
select A,lower(dbo.getcharacter(A)) as B
from tb
when 1 then 'a'
when 2 then 'a,b'
when 3 then 'a,b,c'
...
end
from T
与其写一个合并字串的function,
不如直接穷举 case when 好了
create table [tb]([A] int)
insert [tb]
select 2 union all
select 1 union all
select 3 union all
select 28 union all
select 4 union all
select 2 union all
select 5
--函数
alter function dbo.getcharacter(@id int)
returns varchar(8000)
as
begin
declare @ret varchar(8000) select @ret=isnull(@ret+',','')+code
from
(select case when number>=90 then char(65*(number/90))+char(number-90+65) else char(number) end as code
from master..spt_values
where type = 'P' and number >= 65 and number <= 65+@id-1
) t return @ret
endselect dbo.getcharacter(a) from tb
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A ,B
A
A ,B ,C
A ,B ,C ,D ,E ,F ,G ,H ,I ,J ,K ,L ,M ,N ,O ,P ,Q ,R ,S ,T ,U ,V ,W ,X ,Y ,AA,AB,AC
A ,B ,C ,D
A ,B
A ,B ,C ,D ,E (所影响的行数为 7 行)
就是先将这26个字母select 出来存到游标中
然后根据你的个数如:2
声明@@count
写一个while @@count=0
@@count-1
从游标中读取出来一个(a。b)搞定...