|
有些常见的问题在论坛中不断出现，不妨整理一下。
以下语句是在SQLServer2005上实现的，一些语句无法在SS2000上执行。
有用指数是我根据这个问题的常见程度打的分，仅供参考。实际上，当你遇到了这个问题，这个问题哪怕再少见，解决方案也是非常有用的。
1. 生成若干行记录
有用指数：★★★★★
常见的问题类型：根据起止日期生成若干个日期、生成一天中的各个时间段
《SQL Server 2005技术内幕：T-SQL查询》作者建议在数据库中创建一个数据表：
--自然数表1-1M
CREATE TABLE Nums(n int NOTNULLPRIMARYKEYCLUSTERED)
书上介绍了很多种填充方法，以下是最高效的一种，需要SS2005的ROW_NUMBER()函数。WITH B1 AS(SELECT n=1UNIONALL),2B2 FROM B1 a CROSSJOIN B1 b),128)">4B3 B2 a B2 b),128)">16B4 B3 a B3 b),128)">256B5 B4 a B4 b),128)">65536CTE r=ROW_NUMBER() OVERORDERBY ()) B5 a B3 b) 65536 * 16INSERTINTO Nums(n)
TOP(1000000) r CTE r
有了这个数字表，可以做很多事情，除上面提到的两个外，还有：生成一批测试数据、生成所有ASCII字符或UNICODE中文字符、等等。
经常有高手使用SELECT number FROM master..spt_values WHERE type = 'P'，这是很妙的方法；但这样只有2048个数字，而且语句太长，不够方便。
总之，一个数字辅助表（10万还是100万根据个人需要而定），你值得拥有。
2. 日历表
有用指数：★★★☆☆
《SQL编程风格》一书建议一个企业的数据库应该创建一个日历表：
Calendar(
??? date datetime,
??? weeknum ??? weekday_desc nchar3) bit
)
GO
这个表可以很容易根据第1条的数字辅助表生成出来。如果经常需要进行日期处理的话，或许会需要这个表。
还可以在这个表中包含一些企业关心的特殊日期，比如开盘日休市日（股票行业）、特殊纪念日和节日、重要员工的生日，等等。这些日期往往是很难计算的，比如中国的法定节假日（农历问题）。
3. 字符串的拼接(Join)与切分(Split)
有用指数：★★★★★
这个问题非常常见！开发中经常需要把一组值以逗号分隔拼接在一个字符串，或是反过来把一个逗号分隔的字符串切分成一组值。
用SS2005对XML的支持可以非常方便地实现这个功能。
单变量的拼接与切分：
将一组查询结果按指定分隔符拼接到一个变量中DECLARE@Datebasesvarchar(maxSET STUFF((
??????? ''+name
??????? sys.databases
??????? name
??????? FOR XML PATH('')),
将传入的一个参数按指定分隔符切分到一个表中@SourceIDsa,bcd,123,+-*/=,x&y,<key>' v x.n.value(.varchar(10) (
??? ValuesXML CAST<root>
??????? REPLACE((</v><v></root> XML)
) t
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('')),
???????? 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. 树形结构的存储与查询
有用指数：★★★☆☆
数据库设计中常常会遇到需要存储树形结构，比如员工关系表、组织结构表，等等。
测试数据 #Employees(
??? EmployeeCode 20) #Employees VALUESABCDEFGHIJKLMN/*
可能遇到的查询问题：
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实现员工树形关系表(
???
??????? EmployeeCode,
??????? ReportToCode,
??????? ReportToDepth 0/ EmployeeCode 200))
??? #Employees
??? WHERE ReportToCode IS
???
??????? e.EmployeeCode,
??????? e.ReportToCode,0)"> mgr.ReportToDepth (mgr.ReportToPath e.EmployeeCode #Employees e
??? INNER CTE mgr
??? ON e.ReportToCode mgr.EmployeeCode
)
* ReportToPath
5. IPv4地址的存储与查询
有用指数：★★☆☆☆
IPv4的地址实际上是一个4字节的数据。点分十进制的字符串表示是为了人工读写方便，但范围比较则是原始二进制形式方便。因此需要实现二者的相互转换。
#IPs(
??? strIP 15binary4 #IPs 0.0.0.0255.255.255.255127.0.0.1192.168.43.192192.168.1.10165.54.239.800xB92AEAD30x2D4B2E530x31031B0B0x7C2D5F2F0x473E5D310x90D7D66B
??? strIP,binIP,
??? strIP_new SUBSTRING(binIP,0)">))
??????????????? 2PARSENAME(strIP,
??? intIP_new bigint2566553616777216? int类型也可以，但浪费空间且不直观 #IPs
6. 中文字符处理
有用指数：★★★★☆
SQLServer中文处理涉及到字符集编码和排序规则，是个非常纠结的问题。参看这篇博客。
ASCII字符 n,x=(n NCHAR(n) Nums n BETWEEN32AND126UNICODE中文字符1996840869
??? 0x4E00??? 一
0x9FA5??? ?
以下两个条件用来判断字符串是否包含汉字
LIKE N%[吖-?]% COLLATE Chinese_PRC_CI_AS
%[一-?]% COLLATE Chinese_PRC_BIN
这是因为在以上两种不同的排序规则下，汉字的排列顺序是不同的。
--中文全角标点符号(n),ub(n-65248as 规格3
全角半角标点的转换：
full2halfFUNCTION[dbo]full2half(
@Stringnvarchar)
)
RETURNS
全角(Fullwidth)转换为半角(Halfwidth)
BEGIN@chr)
??? @i PATINDEX(N%[！-～]% COLLATE Latin1_General_BIN,255)">WHILE>
??????? )
??????? UNICODE)))
??????? ENDRETURNhalf2full
半角(Halfwidth)转换为全角(Fullwidth)
%[!-~]%+
7. binary字符串
有用指数：★☆☆☆☆
0x1234与'0x1234'的相互转换。很明显，CAST/CONVERT是不行的。
string到binary可以用这个系统函数sys.fn_varbintohexstr()（实际上是master.dbo.fn_varbintohexstr） sys.fn_varbintohexstr(0x12340x1234binary到string需要自定义函数 dbo.hexstr2varbin(
@hexstrvarbinary
将表示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
@value@ascii@varbinIF0x%UPPER%[^0-9A-F]% COLLATE Chinese_PRC_BIN
??? 0x
??????? <>
??????????? ASCII))
??????????? <=57
??????????????? -48ELSE5516GO
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
