SQLServer常见查询问题
来源:互联网 发布:计算机编程培训班 编辑:程序博客网 时间:2024/06/05 15:33
http://bbs.csdn.net/topics/340078327
1.生成若干行记录
--自然数表1-1MCREATE TABLE Nums(n int NOT NULL PRIMARY KEY CLUSTERED)--书上介绍了很多种填充方法,以下是最高效的一种,需要SS2005的ROW_NUMBER()函数。WITH B1 AS(SELECT n=1 UNION ALL SELECT n=1), --2B2 AS(SELECT n=1 FROM B1 a CROSS JOIN B1 b), --4B3 AS(SELECT n=1 FROM B2 a CROSS JOIN B2 b), --16B4 AS(SELECT n=1 FROM B3 a CROSS JOIN B3 b), --256B5 AS(SELECT n=1 FROM B4 a CROSS JOIN B4 b), --65536CTE AS(SELECT r=ROW_NUMBER() OVER(ORDER BY (SELECT 1)) FROM B5 a CROSS JOIN B3 b) --65536 * 16INSERT INTO Nums(n)SELECT TOP(1000000) r FROM CTE ORDER BY r
2.日历表
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)GOWITH CTE1 AS(SELECTdate = DATEADD(day,n,'19991231')FROM NumsWHERE n <= DATEDIFF(day,'19991231','20201231')),CTE2 AS(SELECTdate,weeknum = DATEPART(week,date),weekday = (DATEPART(weekday,date) + @@DATEFIRST - 1) % 7,weekday_desc = DATENAME(weekday,date)FROM CTE1)--INSERT INTO CalendarSELECTdate,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 ENDFROM CTE2
3.字符串的拼接(Join)与切分(Split)
<strong>--将一组查询结果按指定分隔符拼接到一个变量中DECLARE @Datebases varchar(max)SET @Datebases = STUFF((SELECT ','+nameFROM sys.databasesORDER BY nameFOR 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)) tCROSS APPLY t.ValuesXML.nodes('/root/v') x(n)</strong>
<strong>--测试数据:CREATE TABLE #ToJoin( TableName varchar(20) NOT NULL, ColumnName varchar(20) NOT NULL, PRIMARY KEY CLUSTERED(TableName,ColumnName))GOCREATE TABLE #ToSplit( TableName varchar(20) NOT NULL PRIMARY KEY CLUSTERED, ColumnNames varchar(max) NOT NULL)GOINSERT 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')GOINSERT 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 tGROUP 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) tCROSS APPLY t.ColumnNamesXML.nodes('/Root/ColumnName') c(ColumnName)</strong>
4.树形结构的存储与查询
--测试数据CREATE TABLE #Employees(EmployeeCode varchar(20) NOT NULL PRIMARY KEY CLUSTERED,ReportToCode varchar(20) NULL)GOINSERT 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(SELECTEmployeeCode,ReportToCode,ReportToDepth = 0,ReportToPath = CAST('/' + EmployeeCode + '/' AS varchar(200))FROM #EmployeesWHERE ReportToCode IS NULLUNION ALLSELECTe.EmployeeCode,e.ReportToCode,ReportToDepth = mgr.ReportToDepth + 1,ReportToPath = CAST(mgr.ReportToPath + e.EmployeeCode + '/' AS varchar(200))FROM #Employees eINNER JOIN CTE mgrON e.ReportToCode = mgr.EmployeeCode)SELECT * FROM CTE ORDER BY ReportToPath
5.IPv4地址的存储与查询
<strong>--测试数据CREATE TABLE #IPs(strIP varchar(15) NULL,binIP binary(4) NULL)GOINSERT 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)GOSELECTstrIP,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</strong>
6.中文字符处理
--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 40869199680x4E00一408690x9FA5龥--以下两个条件用来判断字符串是否包含汉字LIKE N'%[吖-咗]%' COLLATE Chinese_PRC_CI_ASLIKE 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 65374SELECT NCHAR(12288),NCHAR(32)652810xFF01!!653740xFF5E~~--以下条件用来判断字符串是否包含全角标点LIKE N'%[!-~]%' COLLATE Chinese_PRC_BIN
--full2halfCREATE FUNCTION [dbo].[full2half](@String nvarchar(max))RETURNS nvarchar(max)AS/*全角(Fullwidth)转换为半角(Halfwidth)*/BEGINDECLARE @chr nchar(1)DECLARE @i intSET @String = REPLACE(@String,N' ',N' ')SET @i = PATINDEX(N'%[!-~]%' COLLATE Latin1_General_BIN,@String)WHILE @i > 0BEGINSET @chr = SUBSTRING(@String,@i,1)SET @String = REPLACE(@String,@chr,NCHAR(UNICODE(@chr)-65248))SET @i = PATINDEX(N'%[!-~]%' COLLATE Latin1_General_BIN,@String)ENDRETURN @StringENDGOCREATE FUNCTION [dbo].[half2full](@String nvarchar(max))RETURNS nvarchar(max)AS/*半角(Halfwidth)转换为全角(Fullwidth)*/BEGINDECLARE @chr nchar(1)DECLARE @i intSET @String = REPLACE(@String,N' ',N' ')SET @i = PATINDEX(N'%[!-~]%' COLLATE Latin1_General_BIN,@String)WHILE @i > 0BEGINSET @chr = SUBSTRING(@String,@i,1)SET @String = REPLACE(@String,@chr,NCHAR(UNICODE(@chr)+65248))SET @i = PATINDEX(N'%[!-~]%' COLLATE Latin1_General_BIN,@String)ENDRETURN @StringENDGO
7.binary字符串
--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进制类型--TESTCASESSELECT dbo.hexstr2varbin(NULL),NULLSELECT dbo.hexstr2varbin(''),0xSELECT dbo.hexstr2varbin('0x'),0xSELECT dbo.hexstr2varbin('30394161'),0x30394161SELECT dbo.hexstr2varbin('0x30394161'),0x30394161SELECT dbo.hexstr2varbin('0x1A2B3C4D5E6F'),0x1A2B3C4D5E6FSELECT dbo.hexstr2varbin('0x1a2b3c4d5e6f'),0x1a2b3c4d5e6f--UNIMPLEMENTEDSELECT dbo.hexstr2varbin('0x3039416'),0x3039416*/BEGINDECLARE @value intDECLARE @ascii intDECLARE @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_BINBEGINSET @varbin = 0xWHILE @hexstr <> ''BEGINSET @value = ASCII(SUBSTRING(@hexstr,1,1))IF @value <= 57SET @value = @value - 48ELSESET @value = @value - 55SET @ascii = @value * 16SET @value = ASCII(SUBSTRING(@hexstr,2,1))IF @value <= 57SET @value = @value - 48ELSESET @value = @value - 55SET @ascii = @ascii + @valueSET @varbin = @varbin + CAST(@ascii AS binary(1))SET @hexstr = STUFF(@hexstr,1,2,'')ENDENDRETURN @varbinENDGO
0 0
- SQLServer常见查询问题
- SQLServer常见查询问题
- SQLServer常见查询问题
- SQLServer常见查询问题
- sqlserver中文查询不出来的问题
- SQLServer查询数据后,插入表中时去重问题
- Mybatis+sqlServer查询速度慢的问题
- SqlServer 模糊查询中文查不出来问题
- 关于SqlServer 查询自增列的问题
- 2011-09-07[sqlserver投票统计查询问题]
- Sqlserver查询的时候是否区分大小写的问题
- sqlserver,oracle ,Mysql数据库 关于区分大小写查询的问题
- 关于sqlserver中的表嵌套查询数据错乱的问题
- SqlServer中的SmallDatetime作为条件查询的截至日期问题!
- SQL多表连接查询以及mysql数据库、sqlserver数据库常见不同点
- SQLServer Replication 常见错误
- SQLServer Replication 常见错误
- SQLServer Replication 常见错误
- mybatis批量和更新(mysql)
- 三、程序的控制结构
- webpack中 publicPath 跟 path 的区别
- lambda表达式
- Android AssetManager <1>
- SQLServer常见查询问题
- Android 平台电容式触摸屏的驱动基本原理
- (2.1.25)关于时间,日期,星期,月份的算法(Java中Calendar的使用方法)
- 读取注册表键值
- 逆向生成MySQL数据库对应的建表与Insert语句(可完成记录的迁移与备份)
- 理解Netty中的Zero-copy
- PC端商城详情页脚本
- Netty之ByteBuf
- 励志名言短句霸气【古诗词版】