SQLServer常见查询问题
|
这是CSDN论坛中一个帖子的内容，我把它copy到博客里面，方便以后查阅。感谢文章的作者feilniu 1. 生成若干行记录
--
自然数表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)
--
测试数据：
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(
''
)),
''
)
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)
--
测试数据
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
? 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
--
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字符串
有用指数：★☆☆☆☆ 0x1234与'0x1234'的相互转换。很明显，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
))
IF
@value
<=
57
SET
@value
=
@value
-
48
ELSE
SET
@value
=
@value
-
55
SET
@ascii
=
@value
*
16
SET
@value
=
ASCII
(
SUBSTRING
(
@hexstr
,
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
,
''
)
END
END
RETURN
@varbin
END
GO
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |