这是CSDN论坛中一个帖子的内容，我把它copy到博客里面，方便以后查阅。感谢文章的作者feilniu
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 , '' ) 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)
需要注意的是，倘若分隔符为“;”或者字符串值中包含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字节的数据。点分十进制的字符串表示是为了人工读写方便，但范围比较则是原始二进制形式方便。因此需要实现二者的相互转换。
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,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中文处理涉及到字符集编码和排序规则，是个非常纠结的问题。参看这篇博客。
-- 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 GO7. 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总结
以上是内存溢出为你收集整理的SQLServer常见查询问题全部内容，希望文章能够帮你解决SQLServer常见查询问题所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）