首页 > 代码库 > t-sql 笔记(2)

t-sql 笔记(2)

1.用标点符号分隔的字符串,转换成表

--    SELECT * FROM dbo.split(‘581:579:519:279:406:361:560‘,‘:‘)CREATE FUNCTION [dbo].[Split] (@Sql VARCHAR (8000), @Splits VARCHAR (10))   RETURNS @temp TABLE (a VARCHAR (100))AS   BEGIN      DECLARE @i   INT      SET @Sql = RTrim (LTrim (@Sql))      SET @i = CharIndex (@Splits, @Sql)      WHILE @i >= 1      BEGIN         INSERT @temp         VALUES (Left (@Sql, @i - 1))         SET @Sql = SubString (@Sql, @i + 1, Len (@Sql) - @i)         SET @i = CharIndex (@Splits, @Sql)      END      IF @Sql <> ‘‘         INSERT @temp         VALUES (@Sql)      RETURN   END

 2.使用 APPLY

http://technet.microsoft.com/zh-cn/library/ms175156%28v=sql.90%29.aspx

使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。表值函数作为右输入,外部表表达式作为左输入。通过对右输入求值来获得左输入每一行的 计算结果,生成的行被组合起来作为最终输出。APPLY 运算符生成的列的列表是左输入中的列集,后跟右输入返回的列的列表。

APPLY 有两种形式:CROSS APPLY 和 OUTER APPLY。CROSS APPLY 仅返回外部表中通过表值函数生成结果集的行。OUTER APPLY 既返回生成结果集的行,也返回不生成结果集的行,其中表值函数生成的列中的值为 NULL。

技术分享
CREATE TABLE Employees(  empid   int         NOT NULL,  mgrid   int         NULL,  empname varchar(25) NOT NULL,  salary  money       NOT NULL,  CONSTRAINT PK_Employees PRIMARY KEY(empid),)CREATE TABLE Departments(  deptid    INT NOT NULL PRIMARY KEY,  deptname  VARCHAR(25) NOT NULL,  deptmgrid INT NULL REFERENCES Employees)CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) RETURNS @TREE TABLE(  empid   INT NOT NULL,  empname VARCHAR(25) NOT NULL,  mgrid   INT NULL,  lvl     INT NOT NULL)ASBEGIN  WITH Employees_Subtree(empid, empname, mgrid, lvl)  AS  (     -- Anchor Member (AM)    SELECT empid, empname, mgrid, 0    FROM Employees    WHERE empid = @empid    UNION all        -- Recursive Member (RM)    SELECT e.empid, e.empname, e.mgrid, es.lvl+1    FROM Employees AS e      JOIN Employees_Subtree AS es        ON e.mgrid = es.empid  )  INSERT INTO @TREE    SELECT * FROM Employees_Subtree  RETURNENDGOSELECT *FROM Departments AS D  CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST
使用 APPLY

 

 

 

 

 

x.待续

t-sql 笔记(2)