请问:
我有一个表temp结构为: no int,编号 char(3)两个字段,请问如何向其插入以下有规律的记录
比如我要insert 100条记录:
no 编号
1 AAA
2 AAB
3 AAC
... ...
... ...
26 AAZ
27 ABA
28 ABB
29 ABC
也就是说26进1,从A--Z,然后第二位又从A-Z,依次循环下去.
直到100条记录为止,或更多(1000)这是一个变量.
我有一个表temp结构为: no int,编号 char(3)两个字段,请问如何向其插入以下有规律的记录
比如我要insert 100条记录:
no 编号
1 AAA
2 AAB
3 AAC
... ...
... ...
26 AAZ
27 ABA
28 ABB
29 ABC
也就是说26进1,从A--Z,然后第二位又从A-Z,依次循环下去.
直到100条记录为止,或更多(1000)这是一个变量.
set @no=0
set @id1='A'
set @id2='A'
set @id3='A'while @no<=1000
begin set @no=@no+1
set @id=@id1+@id2+@id3 insert into tb([no],[id])values(@no,@id)
--print(@no)
--print(@id) if ascii(@id3)>=ascii('Z')
begin
set @id3='A'
if ascii(@id2)>=ascii('Z')
begin
set @id2='A'
if ascii(@id1)>=ascii('Z')
set @id1='A'
else
set @id1=char(ascii(@id1)+1)
end
else
set @id2=char(ascii(@id2)+1)
end
else
set @id3=char(ascii(@id3)+1)
endselect * from tbdrop table tb
RETURNS @Temp TABLE (Value VARCHAR(50),
MaxIndex INT)
AS
BEGIN
DECLARE @IndexTemp TABLE ([Index] INT) DECLARE @INDEX INT SET @INDEX = 65 --- A
WHILE (@INDEX < @M)
BEGIN
INSERT @IndexTemp VALUES (@INDEX)
SET @INDEX = @INDEX + 1
END IF (@N = 1)
BEGIN
INSERT @Temp SELECT char([Index]), [Index] FROM @IndexTemp
END
ELSE
BEGIN
SET @INDEX = @N - 1;
INSERT @Temp
SELECT a.Value + char( b.[Index]), b.[Index]
FROM SelectNFromM(@INDEX, @M) AS a, @IndexTemp b
WHERE b.[Index] > a.MaxIndex
END RETURN
END
GO
SELECT top 100 Value FROM SelectNFromM (3, 91) order by value--'Z'
/*
Value
--------------------------------------------------
ABC
ABD
ABE
ABF
ABG
ABH
ABI
ABJ
ABK
ABL
ABM
ABN
ABO
ABP
ABQ
ABR
ABS
ABT
ABU
ABV
ABW
ABX
ABY
ABZ
ACD
ACE
ACF
ACG
ACH
ACI
ACJ
ACK
ACL
ACM
ACN
ACO
ACP
ACQ
ACR
ACS
ACT
ACU
ACV
ACW
ACX
ACY
ACZ
ADE
ADF
ADG
ADH
ADI
ADJ
ADK
ADL
ADM
ADN
ADO
ADP
ADQ
ADR
ADS
ADT
ADU
ADV
ADW
ADX
ADY
ADZ
AEF
AEG
AEH
AEI
AEJ
AEK
AEL
AEM
AEN
AEO
AEP
AEQ
AER
AES
AET
AEU
AEV
AEW
AEX
AEY
AEZ
AFG
AFH
AFI
AFJ
AFK
AFL
AFM
AFN
AFO
AFP
*/drop function SelectNFromM
RETURNS @Temp TABLE (Value VARCHAR(50),
MaxIndex INT)
AS
BEGIN
DECLARE @IndexTemp TABLE ([Index] INT) DECLARE @INDEX INT SET @INDEX = 65 --- A
WHILE (@INDEX < @M)
BEGIN
INSERT @IndexTemp VALUES (@INDEX)
SET @INDEX = @INDEX + 1
END IF (@N = 1)
BEGIN
INSERT @Temp SELECT char([Index]), [Index] FROM @IndexTemp
END
ELSE
BEGIN
SET @INDEX = @N - 1;
INSERT @Temp
SELECT a.Value + char( b.[Index]), b.[Index]
FROM SelectNFromM(@INDEX, @M) AS a, @IndexTemp b
WHERE b.[Index] >= a.MaxIndex
END RETURN
END
GO
SELECT top 100 Value FROM SelectNFromM (3, 91) order by value--'Z'
/*
Value
--------------------------------------------------
AAA
AAB
AAC
AAD
AAE
AAF
AAG
AAH
AAI
AAJ
AAK
AAL
AAM
AAN
AAO
AAP
AAQ
AAR
AAS
AAT
AAU
AAV
AAW
AAX
AAY
AAZ
ABB
ABC
ABD
ABE
ABF
ABG
ABH
ABI
ABJ
ABK
ABL
ABM
ABN
ABO
ABP
ABQ
ABR
ABS
ABT
ABU
ABV
ABW
ABX
ABY
ABZ
ACC
ACD
ACE
ACF
ACG
ACH
ACI
ACJ
ACK
ACL
ACM
ACN
ACO
ACP
ACQ
ACR
ACS
ACT
ACU
ACV
ACW
ACX
ACY
ACZ
ADD
ADE
ADF
ADG
ADH
ADI
ADJ
ADK
ADL
ADM
ADN
ADO
ADP
ADQ
ADR
ADS
ADT
ADU
ADV
ADW
ADX
ADY
ADZ
AEE
AEF(所影响的行数为 100 行)
*/drop function SelectNFromM
create table tb1(col varchar(10))
insert into tb1 values('A')
insert into tb1 values('B')
insert into tb1 values('C')
insert into tb1 values('D')
insert into tb1 values('E')
insert into tb1 values('F')
insert into tb1 values('G')
insert into tb1 values('H')
insert into tb1 values('I')
insert into tb1 values('J')
insert into tb1 values('K')
insert into tb1 values('L')
insert into tb1 values('M')
insert into tb1 values('N')
insert into tb1 values('O')
insert into tb1 values('P')
insert into tb1 values('Q')
insert into tb1 values('R')
insert into tb1 values('S')
insert into tb1 values('T')
insert into tb1 values('U')
insert into tb1 values('V')
insert into tb1 values('W')
insert into tb1 values('X')
insert into tb1 values('Y')
insert into tb1 values('Z')
create table tb2(col varchar(10))
insert into tb2 values('A')
insert into tb2 values('B')
insert into tb2 values('C')
insert into tb2 values('D')
insert into tb2 values('E')
insert into tb2 values('F')
insert into tb2 values('G')
insert into tb2 values('H')
insert into tb2 values('I')
insert into tb2 values('J')
insert into tb2 values('K')
insert into tb2 values('L')
insert into tb2 values('M')
insert into tb2 values('N')
insert into tb2 values('O')
insert into tb2 values('P')
insert into tb2 values('Q')
insert into tb2 values('R')
insert into tb2 values('S')
insert into tb2 values('T')
insert into tb2 values('U')
insert into tb2 values('V')
insert into tb2 values('W')
insert into tb2 values('X')
insert into tb2 values('Y')
insert into tb2 values('Z')
create table tb3(col varchar(10))
insert into tb3 values('A')
insert into tb3 values('B')
insert into tb3 values('C')
insert into tb3 values('D')
insert into tb3 values('E')
insert into tb3 values('F')
insert into tb3 values('G')
insert into tb3 values('H')
insert into tb3 values('I')
insert into tb3 values('J')
insert into tb3 values('K')
insert into tb3 values('L')
insert into tb3 values('M')
insert into tb3 values('N')
insert into tb3 values('O')
insert into tb3 values('P')
insert into tb3 values('Q')
insert into tb3 values('R')
insert into tb3 values('S')
insert into tb3 values('T')
insert into tb3 values('U')
insert into tb3 values('V')
insert into tb3 values('W')
insert into tb3 values('X')
insert into tb3 values('Y')
insert into tb3 values('Z')
go
select top 100 col from
(
select col = tb1.col + tb2.col + tb3.col from tb1 cross join tb2 cross join tb3
) t
order by coldrop table tb1,tb2,tb3/*
col
------------------------------
AAA
AAB
AAC
AAD
AAE
AAF
AAG
AAH
AAI
AAJ
AAK
AAL
AAM
AAN
AAO
AAP
AAQ
AAR
AAS
AAT
AAU
AAV
AAW
AAX
AAY
AAZ
ABA
ABB
ABC
ABD
ABE
ABF
ABG
ABH
ABI
ABJ
ABK
ABL
ABM
ABN
ABO
ABP
ABQ
ABR
ABS
ABT
ABU
ABV
ABW
ABX
ABY
ABZ
ACA
ACB
ACC
ACD
ACE
ACF
ACG
ACH
ACI
ACJ
ACK
ACL
ACM
ACN
ACO
ACP
ACQ
ACR
ACS
ACT
ACU
ACV
ACW
ACX
ACY
ACZ
ADA
ADB
ADC
ADD
ADE
ADF
ADG
ADH
ADI
ADJ
ADK
ADL
ADM
ADN
ADO
ADP
ADQ
ADR
ADS
ADT
ADU
ADV(所影响的行数为 100 行)
*/
update #T set Col=char(65+(id-1)/(26*26))+char(65+(id-1)/26)+char(65+(id-1)%26)
select * from #T
drop table #T
update #T set Col=char(65+(id-1)/(26*26))+char(65+(id-1)/26)+char(65+(id-1)%26)
select * from #T
drop table #T
select top 26 id=identity(int,65,1) into #T from syscolumns,sysobjects
select top 100 char(a.id)+char(b.id)+char(c.id) from #T a cross join #T b cross join #T c order by a.id,b.id,c.id
drop table #T
--发现用a,c,b的顺序,不用排序
select top 100 char(a.id)+char(c.id)+char(b.id) from #T a cross join #T b cross join #T c -- order by a.id,b.id,c.id
drop table #T
Limpire:有点漏洞
*/select top 100 id=identity(int,1,1),Col=cast(null as char(3)) into #T from syscolumns,sysobjects
--update #T set Col=char(65+(id-1)/(26*26))+char(65+(id-1)/26)+char(65+(id-1)%26)
update #T set Col=char(65+(id-1)/(26*26)%26)+char(65+(id-1)/26%26)+char(65+(id-1)%26)
select * from #T
drop table #T
RETURNS @Temp TABLE (Value VARCHAR(50),
MaxIndex INT)
AS
BEGIN
DECLARE @IndexTemp TABLE ([Index] INT) DECLARE @INDEX INT SET @INDEX = 65 --- A
WHILE (@INDEX < @M)
BEGIN
INSERT @IndexTemp VALUES (@INDEX)
SET @INDEX = @INDEX + 1
END IF (@N = 1)
BEGIN
INSERT @Temp SELECT char([Index]), [Index] FROM @IndexTemp
END
ELSE
BEGIN
SET @INDEX = @N - 1;
INSERT @Temp
SELECT a.Value + char( b.[Index]), b.[Index]
FROM SelectNFromM(@INDEX, @M) AS a, @IndexTemp b
---无枪的狙击手的函数,删除 WHERE b.[Index] >= a.MaxIndex ,就可以了
END RETURN
END
GO
SELECT top 100 Value FROM SelectNFromM (3, 91) order by value--'Z'/*
AAA
AAB
AAC
AAD
AAE
AAF
AAG
AAH
AAI
AAJ
AAK
AAL
AAM
AAN
AAO
AAP
AAQ
AAR
AAS
AAT
AAU
AAV
AAW
AAX
AAY
AAZ
ABA
ABB
ABC
ABD
ABE
ABF
ABG
ABH
ABI
ABJ
ABK
ABL
ABM
ABN
ABO
ABP
ABQ
ABR
ABS
ABT
ABU
ABV
ABW
ABX
ABY
ABZ
ACA
ACB
ACC
ACD
ACE
ACF
ACG
ACH
ACI
ACJ
ACK
ACL
ACM
ACN
ACO
ACP
ACQ
ACR
ACS
ACT
ACU
ACV
ACW
ACX
ACY
ACZ
ADA
ADB
ADC
ADD
ADE
ADF
ADG
ADH
ADI
ADJ
ADK
ADL
ADM
ADN
ADO
ADP
ADQ
ADR
ADS
ADT
ADU
ADV
*/