t-sql 笔记(2)

浏览数：19 / 时间：2015年06月12日

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

  RETURN
END
GO
SELECT *
FROM Departments AS D
  CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST