SQLServer FAQ
118856 ワード
以下の文はSQLServer 2005で実装され、一部の文はSS 2000で実行できない.
1.複数行レコードの生成
有用指数:★★★★
よくある質問のタイプ:
開始日から複数の日付を生成し、1日の期間を生成します.
『SQL Server 2005技術内幕:T-SQLクエリー』の著者は、データベースにデータテーブルを作成することを提案した.
SQL code
この数字表があれば、いろいろなことができます.上記の2つのほかに、次のようなことがあります.
一連のテストデータの生成、ASCII文字またはUNICOD中国語文字のすべての生成など.
よくSELECT number FROM masterを使う達人がいます..spt_values WHERE type='P'は素晴らしい方法です.しかし、2048の数字しかなく、文が長すぎて便利ではありません.
要するに、1つのデジタル補助表(10万か100万かは個人のニーズによって異なります)、
あなたは持つ価値があります.
2.カレンダー表
有用指数:★★☆☆
「SQLプログラミングスタイル」という本は、企業のデータベースにカレンダー表を作成することを提案しています.
SQL code
この表は1条目の数字補助表から容易に生成できる.常に日付処理が必要な場合は、この表が必要かもしれません.
この表には、開場日の休市日(株式業界)、特別記念日と祝日、重要な従業員の誕生日など、企業が関心を持っている特別な日付も含まれています.これらの日付は往々にして計算しにくい.例えば、中国の法定祝日(旧暦問題)である.
3.文字列の結合(Join)と分割(Split)
有用指数:★★★★
この問題はよくあります.開発において常に必要とされる
値のセットをカンマで区切って文字列に接続するか、逆にします.
カンマで区切られた文字列を値のセットに分割します.
この機能はSS 2005のXMLサポートで非常に便利に実現できる.
単一変数の結合と分割:
SQL code
バッチの結合と分割:
SQL code
区切り記号が「;」の場合、注意してください.または、文字列値にXML特殊文字(&、<、>など)が含まれている場合、上記の方法では処理できない場合があります.
4.ツリー構造の保存とクエリー
有用指数:★★☆☆
データベース設計では、従業員リレーションシップテーブル、組織構造テーブルなど、ツリー構造を格納する必要があることがよくあります.
SQL code
5.IPv 4アドレスの保存と照会
有用指数:★☆☆☆
IPv 4のアドレスは実際には4バイトのデータです.ポイント10進数の文字列は、手動で読み書きするために便利であるが、範囲比較は元のバイナリ形式で便利である.そのため,両者の相互変換を実現する必要がある.
SQL code
6.中国語文字処理
有用指数:★★★☆
SQLServerの中国語処理は
文字セットの符号化とソート規則は、非常に葛藤する問題です.参観する
このブログ .
SQL code
全角半角句読点の変換:
SQL code
7.binary文字列
有用指数:★☆☆☆☆
0 x 1234と'0 x 1234'の相互変換.明らかにCAST/CONVERTはダメです.
SQL code
1.複数行レコードの生成
有用指数:★★★★
よくある質問のタイプ:
開始日から複数の日付を生成し、1日の期間を生成します.
『SQL Server 2005技術内幕:T-SQLクエリー』の著者は、データベースにデータテーブルを作成することを提案した.
SQL code
--
1-1M
CREATE
TABLE
Nums(n
int
NOT
NULL
PRIMARY
KEY
CLUSTERED
)
--
， ， SS2005 ROW_NUMBER() 。
WITH
B1
AS
(
SELECT
n
=
1
UNION
ALL
SELECT
n
=
1
),
--
2
B2
AS
(
SELECT
n
=
1
FROM
B1 a
CROSS
JOIN
B1 b),
--
4
B3
AS
(
SELECT
n
=
1
FROM
B2 a
CROSS
JOIN
B2 b),
--
16
B4
AS
(
SELECT
n
=
1
FROM
B3 a
CROSS
JOIN
B3 b),
--
256
B5
AS
(
SELECT
n
=
1
FROM
B4 a
CROSS
JOIN
B4 b),
--
65536
CTE
AS
(
SELECT
r
=
ROW_NUMBER()
OVER
(
ORDER
BY
(
SELECT
1
))
FROM
B5 a
CROSS
JOIN
B3 b)
--
65536 * 16
INSERT
INTO
Nums(n)
SELECT
TOP
(
1000000
) r
FROM
CTE
ORDER
BY
r
この数字表があれば、いろいろなことができます.上記の2つのほかに、次のようなことがあります.
一連のテストデータの生成、ASCII文字またはUNICOD中国語文字のすべての生成など.
よくSELECT number FROM masterを使う達人がいます..spt_values WHERE type='P'は素晴らしい方法です.しかし、2048の数字しかなく、文が長すぎて便利ではありません.
要するに、1つのデジタル補助表(10万か100万かは個人のニーズによって異なります)、
あなたは持つ価値があります.
2.カレンダー表
有用指数:★★☆☆
「SQLプログラミングスタイル」という本は、企業のデータベースにカレンダー表を作成することを提案しています.
SQL code
CREATE
TABLE
Calendar(
date
datetime
NOT
NULL
PRIMARY
KEY
CLUSTERED
,
weeknum
int
NOT
NULL
,
weekday
int
NOT
NULL
,
weekday_desc
nchar
(
3
)
NOT
NULL
,
is_workday
bit
NOT
NULL
,
is_weekend
bit
NOT
NULL
)
GO
WITH
CTE1
AS
(
SELECT
date
=
DATEADD
(
day
,n,
'
19991231
'
)
FROM
Nums
WHERE
n
<=
DATEDIFF
(
day
,
'
19991231
'
,
'
20201231
'
)),
CTE2
AS
(
SELECT
date,
weeknum
=
DATEPART
(week,date),
weekday
=
(
DATEPART
(weekday,date)
+
@@DATEFIRST
-
1
)
%
7
,
weekday_desc
=
DATENAME
(weekday,date)
FROM
CTE1)
--
INSERT INTO Calendar
SELECT
date,
weeknum,
weekday,
weekday_desc,
is_workday
=
CASE
WHEN
weekday
IN
(
0
,
6
)
THEN
0
ELSE
1
END
,
is_weekend
=
CASE
WHEN
weekday
IN
(
0
,
6
)
THEN
1
ELSE
0
END
FROM
CTE2
この表は1条目の数字補助表から容易に生成できる.常に日付処理が必要な場合は、この表が必要かもしれません.
この表には、開場日の休市日(株式業界)、特別記念日と祝日、重要な従業員の誕生日など、企業が関心を持っている特別な日付も含まれています.これらの日付は往々にして計算しにくい.例えば、中国の法定祝日(旧暦問題)である.
3.文字列の結合(Join)と分割(Split)
有用指数:★★★★
この問題はよくあります.開発において常に必要とされる
値のセットをカンマで区切って文字列に接続するか、逆にします.
カンマで区切られた文字列を値のセットに分割します.
この機能はSS 2005のXMLサポートで非常に便利に実現できる.
単一変数の結合と分割:
SQL code
--
DECLARE
@Datebases
varchar
(
max
)
SET
@Datebases
=
STUFF
((
SELECT
'
,
'
+
name
FROM
sys.databases
ORDER
BY
name
FOR
XML PATH(
''
)),
1
,
1
,
''
)
SELECT
@Datebases
--
DECLARE
@SourceIDs
varchar
(
max
)
SET
@SourceIDs
=
'
a,bcd,123,+-*/=,x&y,<key>
'
SELECT
v
=
x.n.value(
'
.
'
,
'
varchar(10)
'
)
FROM
(
SELECT
ValuesXML
=
CAST
(
'
<root>
'
+
REPLACE
((
SELECT
v
=
@SourceIDs
FOR
XML PATH(
''
)),
'
,
'
,
'
</v><v>
'
)
+
'
</root>
'
AS
XML)
) t
CROSS
APPLY t.ValuesXML.nodes(
'
/root/v
'
) x(n)
バッチの結合と分割:
SQL code
--
：
CREATE
TABLE
#ToJoin(
TableName
varchar
(
20
)
NOT
NULL
,
ColumnName
varchar
(
20
)
NOT
NULL
,
PRIMARY
KEY
CLUSTERED
(TableName,ColumnName))
GO
CREATE
TABLE
#ToSplit(
TableName
varchar
(
20
)
NOT
NULL
PRIMARY
KEY
CLUSTERED
,
ColumnNames
varchar
(
max
)
NOT
NULL
)
GO
INSERT
INTO
#ToJoin
VALUES
(
'
tblEmployee
'
,
'
EmployeeCode
'
)
INSERT
INTO
#ToJoin
VALUES
(
'
tblEmployee
'
,
'
EmployeeName
'
)
INSERT
INTO
#ToJoin
VALUES
(
'
tblEmployee
'
,
'
HireDate
'
)
INSERT
INTO
#ToJoin
VALUES
(
'
tblEmployee
'
,
'
JobCode
'
)
INSERT
INTO
#ToJoin
VALUES
(
'
tblEmployee
'
,
'
ReportToCode
'
)
INSERT
INTO
#ToJoin
VALUES
(
'
tblJob
'
,
'
JobCode
'
)
INSERT
INTO
#ToJoin
VALUES
(
'
tblJob
'
,
'
JobTitle
'
)
INSERT
INTO
#ToJoin
VALUES
(
'
tblJob
'
,
'
JobLevel
'
)
INSERT
INTO
#ToJoin
VALUES
(
'
tblJob
'
,
'
DepartmentCode
'
)
INSERT
INTO
#ToJoin
VALUES
(
'
tblDepartment
'
,
'
DepartmentCode
'
)
INSERT
INTO
#ToJoin
VALUES
(
'
tblDepartment
'
,
'
DepartmentName
'
)
GO
INSERT
INTO
#ToSplit
VALUES
(
'
tblDepartment
'
,
'
DepartmentCode,DepartmentName
'
)
INSERT
INTO
#ToSplit
VALUES
(
'
tblEmployee
'
,
'
EmployeeCode,EmployeeName,HireDate,JobCode,ReportToCode
'
)
INSERT
INTO
#ToSplit
VALUES
(
'
tblJob
'
,
'
DepartmentCode,JobCode,JobLevel,JobTitle
'
)
GO
--
(Join)，SQL Server 2005 FOR XML ：
SELECT
t.TableName,
ColumnNames
=
STUFF
(
(
SELECT
'
,
'
+
c.ColumnName
FROM
#ToJoin c
WHERE
c.TableName
=
t.TableName
FOR
XML PATH(
''
)),
1
,
1
,
''
)
FROM
#ToJoin t
GROUP
BY
t.TableName
--
(Split)， SQL Server 2005 XQuery ：
SELECT
t.TableName,
ColumnName
=
c.ColumnName.value(
'
.
'
,
'
varchar(20)
'
)
FROM
(
SELECT
TableName,
ColumnNamesXML
=
CAST
(
'
<Root>
'
+
REPLACE
((
SELECT
ColumnName
=
ColumnNames
FOR
XML PATH(
''
)),
'
,
'
,
'
</ColumnName><ColumnName>
'
)
+
'
</Root>
'
AS
xml)
FROM
#ToSplit
) t
CROSS
APPLY t.ColumnNamesXML.nodes(
'
/Root/ColumnName
'
) c(ColumnName)
区切り記号が「;」の場合、注意してください.または、文字列値にXML特殊文字(&、<、>など)が含まれている場合、上記の方法では処理できない場合があります.
4.ツリー構造の保存とクエリー
有用指数:★★☆☆
データベース設計では、従業員リレーションシップテーブル、組織構造テーブルなど、ツリー構造を格納する必要があることがよくあります.
SQL code
--
CREATE
TABLE
#Employees(
EmployeeCode
varchar
(
20
)
NOT
NULL
PRIMARY
KEY
CLUSTERED
,
ReportToCode
varchar
(
20
)
NULL
)
GO
INSERT
INTO
#Employees
VALUES
(
'
A
'
,
NULL
)
INSERT
INTO
#Employees
VALUES
(
'
B
'
,
'
A
'
)
INSERT
INTO
#Employees
VALUES
(
'
C
'
,
'
A
'
)
INSERT
INTO
#Employees
VALUES
(
'
D
'
,
'
A
'
)
INSERT
INTO
#Employees
VALUES
(
'
E
'
,
'
B
'
)
INSERT
INTO
#Employees
VALUES
(
'
F
'
,
'
B
'
)
INSERT
INTO
#Employees
VALUES
(
'
G
'
,
'
C
'
)
INSERT
INTO
#Employees
VALUES
(
'
H
'
,
'
D
'
)
INSERT
INTO
#Employees
VALUES
(
'
I
'
,
'
D
'
)
INSERT
INTO
#Employees
VALUES
(
'
J
'
,
'
D
'
)
INSERT
INTO
#Employees
VALUES
(
'
K
'
,
'
J
'
)
INSERT
INTO
#Employees
VALUES
(
'
L
'
,
'
J
'
)
INSERT
INTO
#Employees
VALUES
(
'
M
'
,
'
J
'
)
INSERT
INTO
#Employees
VALUES
(
'
N
'
,
'
K
'
)
GO
/*
：
1. 'D'
2. 'D' 2 （ ）
3. 'N' （ ）
4. @EmployeeCode @LevelDown （@EmployeeCode @LevelDown ）
DECLARE @EmployeeCode varchar(20), @LevelDown int;
SET @EmployeeCode = 'D';
SET @LevelDown = 2;
5. @EmployeeCode @LevelUp （@EmployeeCode @LevelUp ）
DECLARE @EmployeeCode varchar(20), @LevelUp int;
SET @EmployeeCode = 'N';
SET @LevelUp = 2;
*/
--
CTE
WITH
CTE
AS
(
SELECT
EmployeeCode,
ReportToCode,
ReportToDepth
=
0
,
ReportToPath
=
CAST
(
'
/
'
+
EmployeeCode
+
'
/
'
AS
varchar
(
200
))
FROM
#Employees
WHERE
ReportToCode
IS
NULL
UNION
ALL
SELECT
e.EmployeeCode,
e.ReportToCode,
ReportToDepth
=
mgr.ReportToDepth
+
1
,
ReportToPath
=
CAST
(mgr.ReportToPath
+
e.EmployeeCode
+
'
/
'
AS
varchar
(
200
))
FROM
#Employees e
INNER
JOIN
CTE mgr
ON
e.ReportToCode
=
mgr.EmployeeCode
)
SELECT
*
FROM
CTE
ORDER
BY
ReportToPath
5.IPv 4アドレスの保存と照会
有用指数:★☆☆☆
IPv 4のアドレスは実際には4バイトのデータです.ポイント10進数の文字列は、手動で読み書きするために便利であるが、範囲比較は元のバイナリ形式で便利である.そのため,両者の相互変換を実現する必要がある.
SQL code
--
CREATE
TABLE
#IPs(
strIP
varchar
(
15
)
NULL
,
binIP
binary
(
4
)
NULL
)
GO
INSERT
INTO
#IPs
VALUES
(
'
0.0.0.0
'
,
NULL
)
INSERT
INTO
#IPs
VALUES
(
'
255.255.255.255
'
,
NULL
)
INSERT
INTO
#IPs
VALUES
(
'
127.0.0.1
'
,
NULL
)
INSERT
INTO
#IPs
VALUES
(
'
192.168.43.192
'
,
NULL
)
INSERT
INTO
#IPs
VALUES
(
'
192.168.1.101
'
,
NULL
)
INSERT
INTO
#IPs
VALUES
(
'
65.54.239.80
'
,
NULL
)
INSERT
INTO
#IPs
VALUES
(
NULL
,
0xB92AEAD3
)
INSERT
INTO
#IPs
VALUES
(
NULL
,
0x2D4B2E53
)
INSERT
INTO
#IPs
VALUES
(
NULL
,
0x31031B0B
)
INSERT
INTO
#IPs
VALUES
(
NULL
,
0x7C2D5F2F
)
INSERT
INTO
#IPs
VALUES
(
NULL
,
0x473E5D31
)
INSERT
INTO
#IPs
VALUES
(
NULL
,
0x90D7D66B
)
GO
SELECT
strIP,binIP,
strIP_new
=
CAST
(
CAST
(
SUBSTRING
(binIP,
1
,
1
)
AS
int
)
AS
varchar
(
3
))
+
'
.
'
+
CAST
(
CAST
(
SUBSTRING
(binIP,
2
,
1
)
AS
int
)
AS
varchar
(
3
))
+
'
.
'
+
CAST
(
CAST
(
SUBSTRING
(binIP,
3
,
1
)
AS
int
)
AS
varchar
(
3
))
+
'
.
'
+
CAST
(
CAST
(
SUBSTRING
(binIP,
4
,
1
)
AS
int
)
AS
varchar
(
3
)),
binIP_new
=
CAST
(
CAST
(
PARSENAME
(strIP,
4
)
AS
int
)
AS
binary
(
1
))
+
CAST
(
CAST
(
PARSENAME
(strIP,
3
)
AS
int
)
AS
binary
(
1
))
+
CAST
(
CAST
(
PARSENAME
(strIP,
2
)
AS
int
)
AS
binary
(
1
))
+
CAST
(
CAST
(
PARSENAME
(strIP,
1
)
AS
int
)
AS
binary
(
1
)),
intIP_new
=
CAST
(
PARSENAME
(strIP,
1
)
AS
bigint
)
+
CAST
(
PARSENAME
(strIP,
2
)
AS
bigint
)
*
256
+
CAST
(
PARSENAME
(strIP,
3
)
AS
bigint
)
*
65536
+
CAST
(
PARSENAME
(strIP,
4
)
AS
bigint
)
*
16777216
--
int ，
FROM
#IPs
6.中国語文字処理
有用指数:★★★☆
SQLServerの中国語処理は
文字セットの符号化とソート規則は、非常に葛藤する問題です.参観する
このブログ .
SQL code
--
ASCII
SELECT
n,x
=
CAST
(n
AS
binary
(
2
)),u
=
NCHAR
(n)
FROM
Nums
WHERE
n
BETWEEN
32
AND
126
--
UNICODE
SELECT
n,x
=
CAST
(n
AS
binary
(
2
)),u
=
NCHAR
(n)
FROM
Nums
WHERE
n
BETWEEN
19968
AND
40869
19968
0x4E00
40869
0x9FA5
--
LIKE
N
'
%[ - ]%
'
COLLATE Chinese_PRC_CI_AS
LIKE
N
'
%[ - ]%
'
COLLATE Chinese_PRC_BIN
--
， 。
--
SELECT
n,x
=
CAST
(n
AS
binary
(
2
)),uq
=
NCHAR
(n),ub
=
NCHAR
(n
-
65248
)
FROM
Nums
WHERE
n
BETWEEN
65281
AND
65374
SELECT
NCHAR
(
12288
),
NCHAR
(
32
)
65281
0xFF01
！ !
65374
0xFF5E
～
~
--
LIKE
N
'
%[！-～]%
'
COLLATE Chinese_PRC_BIN
全角半角句読点の変換:
SQL code
--
full2half
CREATE
FUNCTION
[
dbo
]
.
[
full2half
]
(
@String
nvarchar
(
max
)
)
RETURNS
nvarchar
(
max
)
AS
/*
(Fullwidth) (Halfwidth)
*/
BEGIN
DECLARE
@chr
nchar
(
1
)
DECLARE
@i
int
SET
@String
=
REPLACE
(
@String
,N
'
'
,N
'
'
)
SET
@i
=
PATINDEX
(N
'
%[！-～]%
'
COLLATE Latin1_General_BIN,
@String
)
WHILE
@i
>
0
BEGIN
SET
@chr
=
SUBSTRING
(
@String
,
@i
,
1
)
SET
@String
=
REPLACE
(
@String
,
@chr
,
NCHAR
(
UNICODE
(
@chr
)
-
65248
))
SET
@i
=
PATINDEX
(N
'
%[！-～]%
'
COLLATE Latin1_General_BIN,
@String
)
END
RETURN
@String
END
GO
CREATE
FUNCTION
[
dbo
]
.
[
half2full
]
(
@String
nvarchar
(
max
)
)
RETURNS
nvarchar
(
max
)
AS
/*
(Halfwidth) (Fullwidth)
*/
BEGIN
DECLARE
@chr
nchar
(
1
)
DECLARE
@i
int
SET
@String
=
REPLACE
(
@String
,N
'
'
,N
'
'
)
SET
@i
=
PATINDEX
(N
'
%[!-~]%
'
COLLATE Latin1_General_BIN,
@String
)
WHILE
@i
>
0
BEGIN
SET
@chr
=
SUBSTRING
(
@String
,
@i
,
1
)
SET
@String
=
REPLACE
(
@String
,
@chr
,
NCHAR
(
UNICODE
(
@chr
)
+
65248
))
SET
@i
=
PATINDEX
(N
'
%[!-~]%
'
COLLATE Latin1_General_BIN,
@String
)
END
RETURN
@String
END
GO
7.binary文字列
有用指数:★☆☆☆☆
0 x 1234と'0 x 1234'の相互変換.明らかにCAST/CONVERTはダメです.
SQL code
--
string binary sys.fn_varbintohexstr()（ master.dbo.fn_varbintohexstr）
SELECT
sys.fn_varbintohexstr(
0x1234
),
'
0x1234
'
--
binary string
CREATE
FUNCTION
dbo.hexstr2varbin(
@hexstr
varchar
(
max
)
)
RETURNS
varbinary
(
max
)
AS
/*
16 2
--TESTCASES
SELECT dbo.hexstr2varbin(NULL),NULL
SELECT dbo.hexstr2varbin(''),0x
SELECT dbo.hexstr2varbin('0x'),0x
SELECT dbo.hexstr2varbin('30394161'),0x30394161
SELECT dbo.hexstr2varbin('0x30394161'),0x30394161
SELECT dbo.hexstr2varbin('0x1A2B3C4D5E6F'),0x1A2B3C4D5E6F
SELECT dbo.hexstr2varbin('0x1a2b3c4d5e6f'),0x1a2b3c4d5e6f
--UNIMPLEMENTED
SELECT dbo.hexstr2varbin('0x3039416'),0x3039416
*/
BEGIN
DECLARE
@value
int
DECLARE
@ascii
int
DECLARE
@varbin
varbinary
(
max
)
IF
@hexstr
LIKE
'
0x%
'
SET
@hexstr
=
STUFF
(
@hexstr
,
1
,
2
,
''
)
SET
@hexstr
=
UPPER
(
@hexstr
)
IF
@hexstr
NOT
LIKE
'
%[^0-9A-F]%
'
COLLATE Chinese_PRC_BIN
BEGIN
SET
@varbin
=
0x
WHILE
@hexstr
<>
''
BEGIN
SET
@value
=
ASCII
(
SUBSTRING
(
@hexstr
,
1
,
1
))
IF
@value
<=
57
SET
@value
=
@value
-
48
ELSE
SET
@value
=
@value
-
55
SET
@ascii
=
@value
*
16
SET
@value
=
ASCII
(
SUBSTRING
(
@hexstr
,
2
,
1
))
IF
@value
<=
57
SET
@value
=
@value
-
48
ELSE
SET
@value
=
@value
-
55
SET
@ascii
=
@ascii
+
@value
SET
@varbin
=
@varbin
+
CAST
(
@ascii
AS
binary
(
1
))
SET
@hexstr
=
STUFF
(
@hexstr
,
1
,
2
,
''
)
END
END
RETURN
@varbin
END
GO