SQLServer常见查询问题

feilniu 2010-05-28 04:23:08
加精
有些常见的问题在论坛中不断出现，不妨整理一下。

以下语句是在SQLServer2005上实现的，一些语句无法在SS2000上执行。

有用指数是我根据这个问题的常见程度打的分，仅供参考。实际上，当你遇到了这个问题，这个问题哪怕再少见，解决方案也是非常有用的。


1. 生成若干行记录
有用指数：★★★★★

常见的问题类型：根据起止日期生成若干个日期、生成一天中的各个时间段

《SQL Server 2005技术内幕：T-SQL查询》作者建议在数据库中创建一个数据表：


--自然数表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


有了这个数字表，可以做很多事情，除上面提到的两个外，还有：生成一批测试数据、生成所有ASCII字符或UNICODE中文字符、等等。

经常有高手使用SELECT number FROM master..spt_values WHERE type = 'P'，这是很妙的方法；但这样只有2048个数字，而且语句太长，不够方便。

总之，一个数字辅助表（10万还是100万根据个人需要而定），你值得拥有。


2. 日历表
有用指数：★★★☆☆

《SQL编程风格》一书建议一个企业的数据库应该创建一个日历表：


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)
有用指数：★★★★★

这个问题非常常见！开发中经常需要把一组值以逗号分隔拼接在一个字符串，或是反过来把一个逗号分隔的字符串切分成一组值。
用SS2005对XML的支持可以非常方便地实现这个功能。

单变量的拼接与切分：


--将一组查询结果按指定分隔符拼接到一个变量中

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)


批量的拼接与切分：


--测试数据：

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. 树形结构的存储与查询
有用指数：★★★☆☆

数据库设计中常常会遇到需要存储树形结构，比如员工关系表、组织结构表，等等。



--测试数据

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字节的数据。点分十进制的字符串表示是为了人工读写方便，但范围比较则是原始二进制形式方便。因此需要实现二者的相互转换。



--测试数据

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中文处理涉及到字符集编码和排序规则，是个非常纠结的问题。参看这篇博客。



--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


全角半角标点的转换：


--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




暂时就这些。欢迎拍砖。
...全文
15535 460 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
460 条回复
切换为时间正序
请发表友善的回复…
发表回复
小范f-li.cn 2012-12-25
  • 打赏
  • 举报
 回复
哈哈～学习之～
limitstar 2012-02-24
  • 打赏
  • 举报
 回复
谢谢楼主的分享，你太伟大了
lgq_liang 2012-02-01
  • 打赏
  • 举报
 回复
记录,对我有用
wfymqj 2011-10-06
  • 打赏
  • 举报
 回复
这个收藏了，谢谢分享
tlp080931 2011-01-05
  • 打赏
  • 举报
 回复
谢谢你与大家分享，我们大家应该互相学习
haozi187 2010-11-27
  • 打赏
  • 举报
 回复
好贴·顶起··
abuying 2010-11-01
  • 打赏
  • 举报
 回复
好东西。收藏！
无涯大者 2010-07-22
  • 打赏
  • 举报
 回复
嗯 不错 收藏了哦 谢谢楼主~
Valefish 2010-07-09
  • 打赏
  • 举报
 回复
有用啊?真的有用
yinqi025 2010-07-09
  • 打赏
  • 举报
 回复
好东西 都是为常用的精典之作 收藏收藏
sdlwhgw 2010-07-07
  • 打赏
  • 举报
 回复
学习。。
aspNet123Xu 2010-07-07
  • 打赏
  • 举报
 回复
谢谢，非常好的贴子
cpt_adt2010 2010-07-07
  • 打赏
  • 举报
 回复
貌似很棒哦，支持~~
needacoder 2010-07-07
  • 打赏
  • 举报
 回复
我是来学习的,别问我
wl_fl 2010-07-07
  • 打赏
  • 举报
 回复
不太懂 学习中~
qqzhangmin520 2010-07-06
  • 打赏
  • 举报
 回复
收藏了
v1ctory1216 2010-07-06
  • 打赏
  • 举报
 回复
学习，蹭分，帮顶，收藏
flyingkyo 2010-07-06
  • 打赏
  • 举报
 回复
好帖，学习，收藏！
csdn_风中雪狼 2010-07-06
  • 打赏
  • 举报
 回复
好东西,有些看不懂.
onisland 2010-07-05
  • 打赏
  • 举报
 回复
有难度呀。。
加载更多回复（431）
Microsoft SQL Server 2005技术内幕： T-SQ程序设计.pdf
　Datetime相关的查询问题 　1.2 与字符相关的问题 　模式匹配 　区分大小写(Case-Sensitive)的筛选器 　1.3 大型对象(Large Object，LOB) 　MAX 说明符 　BULK行集提供程序 　1.4 隐式转换(Implicit Conversion) ...
java连接sql server2008所需要的jar包
String url = "jdbc:sqlserver://localhost:1433;databaseName=myDB"; String user = "username"; String password = "password"; try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); ...
SQL语句集锦
6. **查询 时间1.txt**：与时间相关的查询在SQL中很常见，可能包含了日期和时间的处理，如SELECT语句中使用BETWEEN或DATEPART函数来过滤特定时间段的数据。 7. **新建 文本文档.txt**：同样，这个文件可能包含了SQL...
SQL Server 常见问题解析
SQL Server 常见问题解析
SQL Server 查询缓慢问题深度解析与优化策略
SQL Server 查询缓慢问题是一个复杂的系统问题，涉及到查询语句、索引、数据库配置、并发控制等多个方面。通过深入分析查询缓慢的原因，并采取针对性的优化策略，可以有效提高 SQL Server 的查询性能，提升系统的...
疑难问题

22,302

社区成员

121,734

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章