SQLServer常见查询问题.docx
《SQLServer常见查询问题.docx》由会员分享，可在线阅读，更多相关《SQLServer常见查询问题.docx（16页珍藏版）》请在冰豆网上搜索。
SQLServer常见查询问题
有些常见的问题在论坛中不断出现，不妨整理一下。
以下语句是在SQLServer2005上实现的，一些语句无法在SS2000上执行。
有用指数是我根据这个问题的常见程度打的分，仅供参考。
实际上，当你遇到了这个问题，这个问题哪怕再少见，解决方案也是非常有用的。
1.生成若干行记录
有用指数：
★★★★★
常见的问题类型：
根据起止日期生成若干个日期、生成一天中的各个时间段
《SQLServer2005技术内幕：
T-SQL查询》作者建议在数据库中创建一个数据表：
SQLcode
--自然数表1-1M
CREATETABLENums（nintNOTNULLPRIMARYKEYCLUSTERED）
--书上介绍了很多种填充方法，以下是最高效的一种，需要SS2005的ROW_NUMBER（）函数。
WITHB1AS（SELECTn=1UNIONALLSELECTn=1）,--2
B2AS（SELECTn=1FROMB1aCROSSJOINB1b）,--4
B3AS（SELECTn=1FROMB2aCROSSJOINB2b）,--16
B4AS（SELECTn=1FROMB3aCROSSJOINB3b）,--256
B5AS（SELECTn=1FROMB4aCROSSJOINB4b）,--65536
CTEAS（SELECTr=ROW_NUMBER（）OVER（ORDERBY（SELECT1））FROMB5aCROSSJOINB3b）--65536*16
INSERTINTONums（n）
SELECTTOP（1000000）rFROMCTEORDERBYr
有了这个数字表，可以做很多事情，除上面提到的两个外，还有：
生成一批测试数据、生成所有ASCII字符或UNICODE中文字符、等等。
经常有高手使用SELECTnumberFROMmaster..spt_valuesWHEREtype='P'，这是很妙的方法；但这样只有2048个数字，而且语句太长，不够方便。
总之，一个数字辅助表（10万还是100万根据个人需要而定），你值得拥有。
2.日历表
有用指数：
★★★☆☆
《SQL编程风格》一书建议一个企业的数据库应该创建一个日历表：
SQLcode
CREATETABLECalendar（
datedatetimeNOTNULLPRIMARYKEYCLUSTERED,
weeknumintNOTNULL,
weekdayintNOTNULL,
weekday_descnchar（3）NOTNULL,
is_workdaybitNOTNULL,
is_weekendbitNOTNULL
）
GO
WITHCTE1AS（
SELECT
date=DATEADD（day,n,'19991231'）
FROMNums
WHEREn<=DATEDIFF（day,'19991231','20201231'））,
CTE2AS（
SELECT
date,
weeknum=DATEPART（week,date）,
weekday=（DATEPART（weekday,date）+@@DATEFIRST-1）%7,
weekday_desc=DATENAME（weekday,date）
FROMCTE1）
--INSERTINTOCalendar
SELECT
date,
weeknum,
weekday,
weekday_desc,
is_workday=CASEWHENweekdayIN（0,6）THEN0ELSE1END,
is_weekend=CASEWHENweekdayIN（0,6）THEN1ELSE0END
FROMCTE2
这个表可以很容易根据第1条的数字辅助表生成出来。
如果经常需要进行日期处理的话，或许会需要这个表。
还可以在这个表中包含一些企业关心的特殊日期，比如开盘日休市日（股票行业）、特殊纪念日和节日、重要员工的生日，等等。
这些日期往往是很难计算的，比如中国的法定节假日（农历问题）。
3.字符串的拼接（Join）与切分（Split）
有用指数：
★★★★★
这个问题非常常见！
开发中经常需要把一组值以逗号分隔拼接在一个字符串，或是反过来把一个逗号分隔的字符串切分成一组值。
用SS2005对XML的支持可以非常方便地实现这个功能。
单变量的拼接与切分：
SQLcode
--将一组查询结果按指定分隔符拼接到一个变量中
DECLARE@Datebasesvarchar（max）
SET@Datebases=STUFF（（
SELECT','+name
FROMsys.databases
ORDERBYname
FORXMLPATH（''））,1,1,''）
SELECT@Datebases
--将传入的一个参数按指定分隔符切分到一个表中
DECLARE@SourceIDsvarchar（max）
SET@SourceIDs='a,bcd,123,+-*/=,x&y,'
SELECTv=x.n.value（'.','varchar（10）'）
FROM（
SELECTValuesXML=CAST（''+
REPLACE（（SELECTv=@SourceIDsFORXMLPATH（''））,',',''）+
''ASXML）
）t
CROSSAPPLYt.ValuesXML.nodes（'/root/v'）x（n）
批量的拼接与切分：
SQLcode
--测试数据：
CREATETABLE#ToJoin（
TableNamevarchar（20）NOTNULL,
ColumnNamevarchar（20）NOTNULL,
PRIMARYKEYCLUSTERED（TableName,ColumnName））
GO
CREATETABLE#ToSplit（
TableNamevarchar（20）NOTNULLPRIMARYKEYCLUSTERED,
ColumnNamesvarchar（max）NOTNULL）
GO
INSERTINTO#ToJoinVALUES（'tblEmployee','EmployeeCode'）
INSERTINTO#ToJoinVALUES（'tblEmployee','EmployeeName'）
INSERTINTO#ToJoinVALUES（'tblEmployee','HireDate'）
INSERTINTO#ToJoinVALUES（'tblEmployee','JobCode'）
INSERTINTO#ToJoinVALUES（'tblEmployee','ReportToCode'）
INSERTINTO#ToJoinVALUES（'tblJob','JobCode'）
INSERTINTO#ToJoinVALUES（'tblJob','JobTitle'）
INSERTINTO#ToJoinVALUES（'tblJob','JobLevel'）
INSERTINTO#ToJoinVALUES（'tblJob','DepartmentCode'）
INSERTINTO#ToJoinVALUES（'tblDepartment','DepartmentCode'）
INSERTINTO#ToJoinVALUES（'tblDepartment','DepartmentName'）
GO
INSERTINTO#ToSplitVALUES（'tblDepartment','DepartmentCode,DepartmentName'）
INSERTINTO#ToSplitVALUES（'tblEmployee','EmployeeCode,EmployeeName,HireDate,JobCode,ReportToCode'）
INSERTINTO#ToSplitVALUES（'tblJob','DepartmentCode,JobCode,JobLevel,JobTitle'）
GO
--拼接（Join），SQLServer2005的FORXML扩展可以将一个列表转成一个字串：
SELECT
t.TableName,
ColumnNames=STUFF（
（SELECT','+c.ColumnName
FROM#ToJoinc
WHEREc.TableName=t.TableName
FORXMLPATH（''））,
1,1,''）
FROM#ToJoint
GROUPBYt.TableName
--切分（Split），使用SQLServer2005对XQuery的支持：
SELECT
t.TableName,
ColumnName=c.ColumnName.value（'.','varchar（20）'）
FROM（
SELECT
TableName,
ColumnNamesXML=CAST（''+REPLACE（（SELECTColumnName=ColumnNamesFORXMLPATH（''））,',',''）+''ASxml）
FROM#ToSplit
）t
CROSSAPPLYt.ColumnNamesXML.nodes（'/Root/ColumnName'）c（ColumnName）
需要注意的是，倘若分隔符为“;”或者字符串值中包含XML特殊字符（比如&、<、>等等），以上方法可能会无法处理。
4.树形结构的存储与查询
有用指数：
★★★☆☆
数据库设计中常常会遇到需要存储树形结构，比如员工关系表、组织结构表，等等。
SQLcode
--测试数据
CREATETABLE#Employees（
EmployeeCodevarchar（20）NOTNULLPRIMARYKEYCLUSTERED,
ReportToCodevarchar（20）NULL）
GO
INSERTINTO#EmployeesVALUES（'A',NULL）
INSERTINTO#EmployeesVALUES（'B','A'）
INSERTINTO#EmployeesVALUES（'C','A'）
INSERTINTO#EmployeesVALUES（'D','A'）
INSERTINTO#EmployeesVALUES（'E','B'）
INSERTINTO#EmployeesVALUES（'F','B'）
INSERTINTO#EmployeesVALUES（'G','C'）
INSERTINTO#EmployeesVALUES（'H','D'）
INSERTINTO#EmployeesVALUES（'I','D'）
INSERTINTO#EmployeesVALUES（'J','D'）
INSERTINTO#EmployeesVALUES（'K','J'）
INSERTINTO#EmployeesVALUES（'L','J'）
INSERTINTO#EmployeesVALUES（'M','J'）
INSERTINTO#EmployeesVALUES（'N','K'）
GO
/*
可能遇到的查询问题：
1.员工'D'的所有直接下属
2.员工'D'的所有2级以内的下属（包括直接下属和直接下属的下属）
3.员工'N'的所有上级（按报告线顺序列出）
4.员工@EmployeeCode的所有@LevelDown级以内的下属（@EmployeeCode和@LevelDown以变量传入）
DECLARE@EmployeeCodevarchar（20）,@LevelDownint;
SET@EmployeeCode='D';
SET@LevelDown=2;
5.员工@EmployeeCode的所有@LevelUp级以内的上级（@EmployeeCode和@LevelUp以变量传入）
DECLARE@EmployeeCodevarchar（20）,@LevelUpint;
SET@EmployeeCode='N';
SET@LevelUp=2;
*/
--用递归CTE实现员工树形关系表
WITHCTEAS（
SELECT
EmployeeCode,
ReportToCode,
ReportToDepth=0,
ReportToPath=CAST（'/'+EmployeeCode+'/'ASvarchar（200））
FROM#Employees
WHEREReportToCodeISNULL
UNIONALL
SELECT
e.EmployeeCode,
e.ReportToCode,
ReportToDepth=mgr.ReportToDepth+1,
ReportToPath=CAST（mgr.ReportToPath+e.EmployeeCode+'/'ASvarchar（200））
FROM#Employeese
INNERJOINCTEmgr
ONe.ReportToCode=mgr.EmployeeCode
）
SELECT*FROMCTEORDERBYReportToPath
5.IPv4地址的存储与查询
有用指数：
★★☆☆☆
IPv4的地址实际上是一个4字节的数据。
点分十进制的字符串表示是为了人工读写方便，但范围比较则是原始二进制形式方便。
因此需要实现二者的相互转换。
SQLcode
--测试数据
CREATETABLE#IPs（
strIPvarchar（15）NULL,
binIPbinary（4）NULL）
GO
INSERTINTO#IPsVALUES（'0.0.0.0',NULL）
INSERTINTO#IPsVALUES（'255.255.255.255',NULL）
INSERTINTO#IPsVALUES（'127.0.0.1',NULL）
INSERTINTO#IPsVALUES（'192.168.43.192',NULL）
INSERTINTO#IPsVALUES（'192.168.1.101',NULL）
INSERTINTO#IPsVALUES（'65.54.239.80',NULL）
INSERTINTO#IPsVALUES（NULL,0xB92AEAD3）
INSERTINTO#IPsVALUES（NULL,0x2D4B2E53）
INSERTINTO#IPsVALUES（NULL,0x31031B0B）
INSERTINTO#IPsVALUES（NULL,0x7C2D5F2F）
INSERTINTO#IPsVALUES（NULL,0x473E5D31）
INSERTINTO#IPsVALUES（NULL,0x90D7D66B）
GO
SELECT
strIP,binIP,
strIP_new=CAST（CAST（SUBSTRING（binIP,1,1）ASint）ASvarchar（3））+'.'+
CAST（CAST（SUBSTRING（binIP,2,1）ASint）ASvarchar（3））+'.'+
CAST（CAST（SUBSTRING（binIP,3,1）ASint）ASvarchar（3））+'.'+
CAST（CAST（SUBSTRING（binIP,4,1）ASint）ASvarchar（3））,
binIP_new=CAST（CAST（PARSENAME（strIP,4）ASint）ASbinary
（1））+
CAST（CAST（PARSENAME（strIP,3）ASint）ASbinary
（1））+
CAST（CAST（PARSENAME（strIP,2）ASint）ASbinary
（1））+
CAST（CAST（PARSENAME（strIP,1）ASint）ASbinary
（1））,
intIP_new=CAST（PARSENAME（strIP,1）ASbigint）+
CAST（PARSENAME（strIP,2）ASbigint）*256+
CAST（PARSENAME（strIP,3）ASbigint）*65536+
CAST（PARSENAME（strIP,4）ASbigint）*16777216--int类型也可以，但浪费空间且不直观
FROM#IPs
6.中文字符处理
有用指数：
★★★★☆
SQLServer中文处理涉及到字符集编码和排序规则，是个非常纠结的问题。
参看这篇博客。
SQLcode
--ASCII字符
SELECTn,x=CAST（nASbinary
（2））,u=NCHAR（n）FROMNumsWHEREnBETWEEN32AND126
--UNICODE中文字符
SELECTn,x=CAST（nASbinary
（2））,u=NCHAR（n）FROMNumsWHEREnBETWEEN19968AND40869
199680x4E00一
408690x9FA5龥
--以下两个条件用来判断字符串是否包含汉字
LIKEN'%[吖-咗]%'COLLATEChinese_PRC_CI_AS
LIKEN'%[一-龥]%'COLLATEChinese_PRC_BIN
--这是因为在以上两种不同的排序规则下，汉字的排列顺序是不同的。
--中文全角标点符号
SELECTn,x=CAST（nASbinary
（2））,uq=NCHAR（n）,ub=NCHAR（n-65248）FROMNumsWHEREnBETWEEN65281AND65374
SELECTNCHAR（12288）,NCHAR（32）
652810xFF01！
!
653740xFF5E～~
--以下条件用来判断字符串是否包含全角标点
LIKEN'%[！
-～]%'COLLATEChinese_PRC_BIN
全角半角标点的转换：
SQLcode
--full2half
CREATEFUNCTION[dbo].[full2half]（
@Stringnvarchar（max）
）
RETURNSnvarchar（max）
AS
/*
全角（Fullwidth）转换为半角（Halfwidth）
*/
BEGIN
DECLARE@chrnchar
（1）
DECLARE@iint
SET@String=REPLACE（@String,N' ',N''）
SET@i=PATINDEX（N'%[！
-～]%'COLLATELatin1_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'%[！
-～]%'COLLATELatin1_General_BIN,@String）
END
RETURN@String
END
GO
CREATEFUNCTION[dbo].[half2full]（
@Stringnvarchar（max）
）
RETURNSnvarchar（max）
AS
/*
半角（Halfwidth）转换为全角（Fullwidth）
*/
BEGIN
DECLARE@chrnchar
（1）
DECLARE@iint
SET@String=REPLACE（@String,N'',N' '）
SET@i=PATINDEX（N'%[!
-~]%'COLLATELatin1_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'%[!
-~]%'COLLATELatin1_General_BIN,@String）
END
RETURN@String
END
GO
暂时就这些。
欢迎拍砖。
∙对我有用[3]
∙丢个板砖[0]
∙引用
∙举报
∙管理
∙TOP
回复次数：
289
∙
∙feilniu
∙（feilniu）
∙
∙等 级：
∙
#1楼得分：
0回复于：
2010-05-2816:
23:
27
7.binary字符串
有用指数：
★☆☆☆☆
0x1234与'0x1234'的相互转换。
很明显，CAST/CONVERT是不行的。
SQLcode
--string到binary可以用这个系统函数sys.fn_varbintohexstr（）（实际上是master.dbo.fn_varbintohexstr）
SELECTsys.fn_varbintohexstr（0x1234）,'0x1234'
--binary到string需要自定义函数
CREATEFUNCTIONdbo.hexstr2varbin（
@hexstrvarchar（max）
）
RETURNSvarbinary（max）
AS
/*
将表示16进制的字符串转换为2进制类型
--TESTCASES
SELECTdbo.hexstr2varbi
升级会员 