日期:2014-05-18  浏览次数:20774 次

sql语句问题在线等立即给分(100分)
表a 字段 id pwd

自动生成id 0-99999 pwd 随机

------解决方案--------------------
SQL code
create table #t(
id int,
pwd char(5)
)
declare @i int
set @i=1
while @i<1000
begin
    insert #t values(@i,right(10000+convert(bigint,abs(checksum(newid()))),5))
    set @i=@i+1
end

select * from #t
drop table #t

------解决方案--------------------
SQL code

select top 10000  identity(int,0,1) id,newid() pwd into # from syscolumns a,syscolumns b

select * from #

------解决方案--------------------
SQL code


select  id=right('000000'+convert(varchar(10),id),5),pwd from #

------解决方案--------------------
CREATE TABLE t
(
id VARCHAR(9),
pwd VARCHAR(6)
)


 SELECT TOP 99999 id=IDENTITY(INT,1,1),
pwd = CHAR(65+RAND(CHECKSUM(NEWID()))*25)+CHAR(65+RAND(CHECKSUM(NEWID()))*25)+
CHAR(65+RAND(CHECKSUM(NEWID()))*25)+CHAR(65+RAND(CHECKSUM(NEWID()))*25)+
CHAR(65+RAND(CHECKSUM(NEWID()))*25)+CHAR(65+RAND(CHECKSUM(NEWID()))*25)
 INTO #
 FROM SYSOBJECTS A, SYSOBJECTS B




INSERT INTO T
SELECT REPLICATE('0', 5 - DATALENGTH(CAST(id AS VARCHAR)))+CAST(ID AS VARCHAR),PWD FROM #

SELECT * FROM t

DROP TABLE #
DROP TABLE T

------解决方案--------------------
SQL code

select top 100000  identity(int,0,1) id,left(newid(),6) pwd into # from syscolumns a,syscolumns b


select  id=right('000000'+convert(varchar(10),id),5),pwd from #

------解决方案--------------------
SQL code
select top 10000  identity(int,0,1) id into tmp from syscolumns a,syscolumns b

select right('0000' + cast(id as varchar),5) id ,newid() pwd from tmp

drop table tmp

/*
id         pwd                                  
---------- ------------------------------------ 
00000      457E4091-B8D7-4162-9A85-4313C458EE5B
00001      741749DE-076D-4ED5-B783-80C7C1807C9E
00002      636F9A57-B484-4751-A3E6-CD71B491F415
00003      C4DF3323-3D9B-4238-B5B8-882D4AF13909
00004      E1829729-C4DD-4B5A-BFBA-FA5F05A385E5
00005      801B1654-D65D-4BD8-822E-E335E262C0D4
00006      A45AD29A-333F-4BA7-B811-5EF2F529B1D0
00007      D05D937F-C0B7-4081-AA94-B94151333428
..............
*/

------解决方案--------------------
太多正确答案了。
------解决方案--------------------
SQL code

select top 5 identity(int,0,1) id,newid() pwd into # from syscolumns a,syscolumns b

select '0-'+right('00000'+convert(varchar(5),convert(int,right(id,5))+1),5) as id,pwd from #
/*
0-00001    E52529E1-BBEE-4F82-83ED-9BCA305ECEC5
0-00002    F0DD2E19-42AF-4AA9-ACA7-6170D71EBDF8
0-00003    8DA2BDCE-3054-4029-BD8E-86DCEBC3CEEC
0-00004    94DB98B9-787B-4C06-B97A-3C7FABE22FEC
0-00005    3015B48B-36DB-4A67-A938-BA99D260EA9A
*/