• SQLServer常见查询问题


    

    http://bbs.csdn.net/topics/340078327

    1.生成若干行记录

    --自然数表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
    

    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
    )
    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
    

    3.字符串的拼接(Join)与切分(Split)

    <strong>--将一组查询结果按指定分隔符拼接到一个变量中
    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)
    </strong>


    <strong>--测试数据:
    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)
    </strong>

    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地址的存储与查询

    <strong>--测试数据
    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)) +
    				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 40869
    19968	0x4E00	一
    40869	0x9FA5	龥
    --以下两个条件用来判断字符串是否包含汉字
    LIKE N'%[吖-咗]%' COLLATE Chinese_PRC_CI_AS
    LIKE 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 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,@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
    

    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进制类型
    --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,1))
    			IF @value <= 57
    				SET @value = @value - 48
    			ELSE
    				SET @value = @value - 55
    			SET @ascii = @value * 16
    			SET @value = ASCII(SUBSTRING(@hexstr,2,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,1,2,'')
    		END
    	END
    	RETURN @varbin
    END
    GO
    



    
  • 相关阅读:
    pdf.js安装步骤和使用
    PDFObject.js、jquerymedia.js、pdf.js的对比
    7 Best jQuery & JavaScript PDF Viewer plugin with examples
    Linux 内核:设备驱动模型(4)uevent与热插拔
    Linux 内核:设备驱动模型(3)class与device
    Linux 内核:设备驱动模型(2)driver-bus-device与probe
    Linux 内核:sysfs 有关的API
    Linux 内核:设备驱动模型(1)sysfs与kobject基类
    在Linux驱动中使用LED子系统
    Linux 驱动:LED子系统
  • 原文地址:https://www.cnblogs.com/sui84/p/6777026.html
Copyright © 2020-2023  润新知