批量生成问题(冲值卡)
数据库里包含这两个字段，都是由系统顺序和随机生成
比如卡号 生成50个
MLAA00000001
MLAA00000050
下次在生成根据最后一个50 再生成50个
MLAA00000050
MLAA00000100
密码10位英文数字大小写(不需要0和o)
生成后具体结构为:
卡号 密码
MLAA00000001 si34KFds43
----------- ----------
求SQL语句，分不够在开～
[解决办法]
Create Table TEST
(卡号Char(12),
密码Char(10))
GO
Select TOP 50 ID = Identity(Int, 1, 1) Into #T From SysColumns
--生成50條隨機數據
Insert TEST
Select
TOP 50
'MLAA ' + Right(100000000 + IsNull(Right((Select Max(卡号) From TEST), 4), 0) + ID, 8),
Left(Replace(Replace(Replace(NewID(), '- ', ' '), '0 ', ' '), 'o ', ' '), 10)
From #T
Select * From TEST
--再生成50條隨機數據
Insert TEST
Select
TOP 50
'MLAA ' + Right(100000000 + IsNull(Right((Select Max(卡号) From TEST), 4), 0) + ID, 8),
Left(Replace(Replace(Replace(NewID(), '- ', ' '), '0 ', ' '), 'o ', ' '), 10)
From #T
Select * From TEST
Drop Table #T
GO
Drop Table TEST
--Result
/*
--第一次生成後表中數據
卡号密码
MLAA000000014E9BDE8497
MLAA00000002A7E17F8F62
MLAA00000003617685DA13
MLAA00000004CA459B8AEC
MLAA0000000559C829D9DE
MLAA0000000631CA6FD98E
MLAA000000074EF3DC4F69
MLAA000000081F6C6A7467
MLAA0000000987B8AB9742
MLAA00000010B4DC88721B
...
MLAA00000040FFDFE2F65C
MLAA000000412414C5FC26
MLAA00000042C3A31382B8
MLAA0000004344AED427AE
MLAA00000044F9C465E1F8
MLAA00000045B5C39D795A
MLAA00000046E6B82AA9C5
MLAA0000004769BE98CC7F
MLAA000000484BEEE9B7A4
MLAA000000493BB9245AC3
MLAA00000050B75B8F96AB
--第二次生成後表中數據
卡号密码
MLAA000000014E9BDE8497
MLAA00000002A7E17F8F62
MLAA00000003617685DA13
MLAA00000004CA459B8AEC
MLAA0000000559C829D9DE
MLAA0000000631CA6FD98E
MLAA000000074EF3DC4F69
MLAA000000081F6C6A7467
MLAA0000000987B8AB9742
MLAA00000010B4DC88721B
...
MLAA00000040FFDFE2F65C
MLAA000000412414C5FC26
MLAA00000042C3A31382B8
MLAA0000004344AED427AE
MLAA00000044F9C465E1F8
MLAA00000045B5C39D795A
MLAA00000046E6B82AA9C5
MLAA0000004769BE98CC7F
MLAA000000484BEEE9B7A4
MLAA000000493BB9245AC3
MLAA00000050B75B8F96AB
MLAA00000051AD7541811C
MLAA00000052459A6B8AA3
MLAA00000053957B39A869
MLAA00000054168DC66185
MLAA0000005517C889742F
MLAA0000005635AC4D71C7
MLAA00000057ACE59CEF6E
MLAA00000058E41B2D3EFD
MLAA000000597D17A5EF6A
MLAA000000604F53CD941E
...
MLAA0000009171E44AC1CB
MLAA000000922C16B6126F
MLAA00000093AACAB9C9FB
MLAA0000009418291C3CFF
MLAA0000009546C3D2DBD6
MLAA00000096DE5495A7BE
MLAA000000975E3221297A
MLAA00000098F79488CEB6
MLAA0000009949C327E639
MLAA0000010091E3EF4F63
*/
[解决办法]
看看我的
Create table AA (ID nvarchar(12),PW nvarchar(10))
insert into AA
select 'MLAA00000001 ', 'si34KFds43 '
union select 'MLAA00000050 ', 'su34KFds43 '
Declare @letter as nvarchar(40)
Declare @lett01 as nvarchar(40)
declare @number as nvarchar(10)
declare @sql as nvarchar(4000)
declare @i as int
declare @j as int
declare @ID as nvarchar(20)
declare @IDN as nvarchar(20)
declare @PW AS Nvarchar(10)
set @letter= 'ABCDEFGHIJKLMNPQRSTUVWXYZ '
set @lett01= 'abcdefghijklmnpqrstuvwxyz '
set @number= '123456789 '
set @i=1
select @IDN=max(Convert(int, substring(id,5,8))) ,@ID=max(id) from aa
select @ID=replace(@ID,@IDN, ' ')
print @IDN
print @ID
while @i <=50
begin
if len(@IDN+1)> len(@IDN)
begin
set @ID=substring(@ID,1,len(@ID)-1)
end
set @IDN=@IDN+1
print stuff(@IDN,1,0,@ID)
select @PW=
substring(@lett01, Convert(int,rand()*25)+1,1)+
substring(@lett01, Convert(int,rand()*25)+1,1)+
substring(@number, Convert(int,rand()*9)+1,1)+
substring(@number, Convert(int,rand()*9)+1,1)+
substring(@letter, Convert(int,rand()*25)+1,1)+
substring(@letter, Convert(int,rand()*25)+1,1)+
substring(@lett01, Convert(int,rand()*25)+1,1)+
substring(@lett01, Convert(int,rand()*25)+1,1)+
substring(@number, Convert(int,rand()*9)+1,1)+
substring(@number, Convert(int,rand()*9)+1,1)
print @PW
insert into AA values (stuff(@IDN,1,0,@ID),@PW)
print @i
set @i=@i+1
end
