22,209
社区成员
发帖
与我相关
我的任务
分享
if Object_id('NInc')is not null
drop function NInc
go
--@NCode进制码表,例如:我们熟悉的16进制为"0123456789ABCDEF",也可以自定义特殊的进制,例如三进制"ABC",这样计算就会有A+1=B,B+1=C,C+1=BA,是不是很神奇?
--@NNum 表示参与运算的N进制数 ,@Num参与运算的10十进制数
create function NInc(@NCode varchar(100),@NNum varchar(10),@Num int)
returns varchar(10)
as
BEGIN
declare @N int,@i int,@idx int,@str varchar(20)
--N进制转十进制,同时+@Num
select @N=len(@NCode),@i=0,@NNum=reverse(@NNum)
while @i<len(@NNum)
begin
set @idx=charindex(substring(@NNum,@i+1,1),@NCode)-1
set @Num=@Num+ @idx*power(@N,@i)
set @i=@i+1
end
set @str=''
--十进制转N进制
while @Num<>0
begin
select @str=substring(@NCode,(@Num % @n)+1,1)+@str
set @Num=@Num/@N
end
if @Num=0 and @str=''
set @str=Left(@NCode,1)
return @str
END
go
楼主可以直接调用
select A=NInc('0123456789ABCDEFGHJKLMNPQRSTUVWXYZ’,'ABCD',1)
实现ABCD+1的操作
-- 自增值
CREATE SEQUENCE dbo.sq_id as bigint START WITH 1;
GO
-- 34 进制
CREATE FUNCTION dbo.f(@id bigint)
RETURNS char(4)
AS
BEGIN;
DECLARE @re varchar(10) = '', @v int;
WHILE @id > 0
SELECT
@v = @id % 34, @id = @id / 34,
@re = CASE
WHEN @v < 10 THEN RTRIM(@v)
ELSE CHAR(55+@v + CASE WHEN @v > 23 THEN 1 ELSE 0 END)
END + @re
RETURN(RIGHT('0000000000' + @re, 4))
END;
GO
-- 测试取 10000 个 ID
SELECT *, dbo.f(NEXT VALUE FOR dbo.sq_id) FROM(
SELECT TOP(1000) ROW_NUMBER() OVER(ORDER BY GETDATE()) as id FROM sys.all_columns
) A
GO
-- 删除测试
DROP FUNCTION dbo.f;
DROP SEQUENCE dbo.sq_id;