开发过程中,需要用到26个字母的一个临时表,一开始想到的方法如下(SQL Server 2008 R2执行):
代码
IF
OBJECT_ID
(
'
#TempDriveInfo
'
)
IS
NOT
NULL
DROP TABLE #TempDriveInfo
CREATE TABLE #TempDriveInfo
(
[ DriveInfoId ] TINYINT IDENTITY PRIMARY KEY NOT NULL ,
[ DriveName ] NVARCHAR ( 10 )
)
INSERT INTO #TempDriveInfo ( [ DriveName ] ) VALUES ( ' A ' ),( ' B ' ),( ' C ' ),( ' D ' ),( ' E ' ),( ' F ' ),( ' G ' ),( ' H ' ),( ' I ' ),( ' J ' ),( ' K ' ),( ' L ' ),( ' M ' ),( ' N ' ),( ' O ' ),( ' P ' ),( ' Q ' ),( ' R ' ),( ' S ' ),( ' T ' ),( ' U ' ),( ' V ' ),( ' W ' ),( ' X ' ),( ' Y ' ),( ' Z ' )
SELECT * FROM #TempDriveInfo
DROP TABLE #TempDriveInfo
CREATE TABLE #TempDriveInfo
(
[ DriveInfoId ] TINYINT IDENTITY PRIMARY KEY NOT NULL ,
[ DriveName ] NVARCHAR ( 10 )
)
INSERT INTO #TempDriveInfo ( [ DriveName ] ) VALUES ( ' A ' ),( ' B ' ),( ' C ' ),( ' D ' ),( ' E ' ),( ' F ' ),( ' G ' ),( ' H ' ),( ' I ' ),( ' J ' ),( ' K ' ),( ' L ' ),( ' M ' ),( ' N ' ),( ' O ' ),( ' P ' ),( ' Q ' ),( ' R ' ),( ' S ' ),( ' T ' ),( ' U ' ),( ' V ' ),( ' W ' ),( ' X ' ),( ' Y ' ),( ' Z ' )
SELECT * FROM #TempDriveInfo
上面这个方法,虽然没有什么问题,但始终觉得不好,还是想到SQL Server的CHAR()函数,把INSERT语句改了:
代码
INSERT
INTO
#TempDriveInfo (
[
DriveName
]
)
VALUES
(
CHAR
(
65
)),(
CHAR
(
66
)),(
CHAR
(
67
)),(
CHAR
(
68
)),(
CHAR
(
69
)),(
CHAR
(
70
)),(
CHAR
(
71
)),(
CHAR
(
72
)),(
CHAR
(
73
)),(
CHAR
(
74
)),(
CHAR
(
75
)),(
CHAR
(
76
)),(
CHAR
(
77
)),(
CHAR
(
78
)),(
CHAR
(
79
)),(
CHAR
(
80
)),(
CHAR
(
81
)),(
CHAR
(
82
)),(
CHAR
(
83
)),(
CHAR
(
84
)),(
CHAR
(
85
)),(
CHAR
(
86
)),(
CHAR
(
87
)),(
CHAR
(
88
)),(
CHAR
(
89
)),(
CHAR
(
90
))
经上面的修改,可以实现相同的结果,还是觉得它很繁杂一样,不够简洁,想到它是连续性,想到使用循环方法。
代码
DECLARE
@FirstCapitalLetters
INT
=
65
WHILE @FirstCapitalLetters <= 90
BEGIN
INSERT INTO #TempDriveInfo ( [ DriveName ] ) VALUES ( CHAR ( @FirstCapitalLetters ))
SET @FirstCapitalLetters = @FirstCapitalLetters + 1
END
WHILE @FirstCapitalLetters <= 90
BEGIN
INSERT INTO #TempDriveInfo ( [ DriveName ] ) VALUES ( CHAR ( @FirstCapitalLetters ))
SET @FirstCapitalLetters = @FirstCapitalLetters + 1
END