SQLServer常见查询问题
发布时间：2020-12-12 15:19:09 所属栏目：MsSql教程 来源：网络整理
导读：以下语句是在SQLServer2005上实现的，一些语句无法在SS2000上执行。 有用指数是我根据这个问题的常见程度打的分，仅供参考。实际上，当你遇到了这个问题，这个问题哪怕再少见，解决方案也是非常有用的。 1. 生成若干行记录 有用指数：★★★★★ 常见的问题
|
以下语句是在SQLServer2005上实现的，一些语句无法在SS2000上执行。
--
自然数表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
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
--
将一组查询结果按指定分隔符拼接到一个变量中
DECLARE
@Datebases
varchar
(
max
)
SET
@Datebases
=
STUFF
((
SELECT
'
,
'
+
name
FROM
sys.databases
ORDER
BY
name
FOR
XML PATH(
''
)),
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
'
,
'
DepartmentName
'
)
GO
INSERT
INTO
#ToSplit
VALUES
(
'
tblDepartment
'
,
'
DepartmentCode,DepartmentName
'
)
INSERT
INTO
#ToSplit
VALUES
(
'
tblEmployee
'
,
'
EmployeeCode,EmployeeName,HireDate,JobCode,ReportToCode
'
)
INSERT
INTO
#ToSplit
VALUES
(
'
tblJob
'
,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
,
''
)
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. IPv4地址的存储与查询 有用指数：★★☆☆☆ IPv4的地址实际上是一个4字节的数据。点分十进制的字符串表示是为了人工读写方便，但范围比较则是原始二进制形式方便。因此需要实现二者的相互转换。 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
)
AS
int
)
AS
varchar
(
3
))
+
'
.
'
+
CAST
(
CAST
(
SUBSTRING
(binIP,
2
,
3
,
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,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,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,
NCHAR
(
UNICODE
(
@chr
)
+
65248
))
SET
@i
=
PATINDEX
(N
'
%[!-~]%
'
COLLATE Latin1_General_BIN,
@String
)
END
RETURN
@String
END
GO
7. binary字符串
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |