批量生成问题(冲值卡)
数据库里包含这两个字段,都是由系统顺序和随机生成
比如卡号 生成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
/*
--第一次生成後表中數據
卡号 密码
MLAA00000001 4E9BDE8497
MLAA00000002 A7E17F8F62
MLAA00000003 617685DA13
MLAA00000004 CA459B8AEC
MLAA00000005 59C829D9DE
MLAA00000006 31CA6FD98E
MLAA00000007 4EF3DC4F69
MLAA00000008 1F6C6A7467
MLAA00000009 87B8AB9742
MLAA00000010 B4DC88721B
...
MLAA00000040 FFDFE2F65C
MLAA00000041 2414C5FC26
MLAA00000042 C3A31382B8
MLAA00000043 44AED427AE
MLAA00000044 F9C465E1F8
MLAA00000045 B5C39D795A
MLAA00000046 E6B82AA9C5
MLAA00000047 69BE98CC7F
MLAA00000048 4BEEE9B7A4
MLAA00000049 3BB9245AC3
MLAA00000050 B75B8F96AB
--第二次生成後表中數據
卡号 密码
MLAA00000001 4E9BDE8497
MLAA00000002 A7E17F8F62
MLAA00000003 617685DA13
MLAA00000004 CA459B8AEC
MLAA00000005 59C829D9DE
MLAA00000006 31CA6FD98E
MLAA00000007 4EF3DC4F69
MLAA00000008 1F6C6A7467
MLAA00000009 87B8AB9742
MLAA00000010 B4DC88721B
...
MLAA00000040 FFDFE2F65C
MLAA00000041 2414C5FC26
MLAA00000042 C3A31382B8
MLAA00000043 44AED427AE
MLAA00000044 F9C465E1F8
MLAA00000045 B5C39D795A
MLAA00000046 E6B82AA9C5
MLAA00000047 69BE98CC7F
MLAA00000048 4BEEE9B7A4
MLAA00000049 3BB9245AC3
MLAA00000050 B75B8F96AB
MLAA00000051 AD7541811C
MLAA00000052 459A6B8AA3
MLAA00000053 957B39A869
MLAA00000054 168DC66185
MLAA00000055 17C889742F
MLAA00000056 35AC4D71C7
MLAA00000057 ACE59CEF6E
MLAA00000058 E41B2D3EFD
MLAA00000059 7D17A5EF6A
MLAA00000060 4F53CD941E
...
MLAA00000091 71E44AC1CB
MLAA00000092 2C16B6126F
MLAA00000093 AACAB9C9FB
MLAA00000094 18291C3CFF
MLAA00000095 46C3D2DBD6
MLAA00000096 DE5495A7BE
MLAA00000097 5E3221297A
MLAA00000098 F79488CEB6
MLAA00000099 49C327E639
MLAA00000100 91E3EF4F63
*/
------解决方案-