SQL数据库常见查询问题

 

摘自：http://topic.csdn.net/u/20100528/16/f3c160a2-6d97-4e19-8f74-154d34a940d7.html?seed=1574840243&r=65853880#r_65853880

 

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

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

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

SQL code

<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ --> -- 自然数表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编程风格》一书建议一个企业的数据库应该创建一个日历表：

SQL code

<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ --> 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的支持可以非常方便地实现这个功能。

单变量的拼接与切分：

SQL code

<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ --> -- 将一组查询结果按指定分隔符拼接到一个变量中 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)

批量的拼接与切分：

SQL code

<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ --> -- 测试数据： 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. 树形结构的存储与查询
有用指数：★★★☆☆

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

SQL code

<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ --> -- 测试数据 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

<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ --> -- 测试数据 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