Sql学习第六天——SQL巩固练习

Sql学习第六天——SQL巩固练习

 

Sql学习第五天——SQL关于apply的两种形式cross apply和outer apply

http://www.2cto.com/database/201304/206330.html

 

在查看SQL Server 2005的帮助文档中的cross apply 运算符时发现了一个个人感觉用到的知识点儿比较多，比较经典的例子。在此写下来，也是为了巩固一下前几天的知识点。

 

先建表一员工表（Employees）：

 

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

)

 

向Employees表中插入数据：

 

--插入数据

INSERT INTO Employees VALUES(1 , NULL, 'Nancy'   , $10000.00)

INSERT INTO Employees VALUES(2 , 1   , 'Andrew'  , $5000.00)

INSERT INTO Employees VALUES(3 , 1   , 'Janet'   , $5000.00)

INSERT INTO Employees VALUES(4 , 1   , 'Margaret', $5000.00) 

INSERT INTO Employees VALUES(5 , 2   , 'Steven'  , $2500.00)

INSERT INTO Employees VALUES(6 , 2   , 'Michael' , $2500.00)

INSERT INTO Employees VALUES(7 , 3   , 'Robert'  , $2500.00)

INSERT INTO Employees VALUES(8 , 3   , 'Laura'   , $2500.00)

INSERT INTO Employees VALUES(9 , 3   , 'Ann'     , $2500.00)

INSERT INTO Employees VALUES(10, 4   , 'Ina'     , $2500.00)

INSERT INTO Employees VALUES(11, 7   , 'David'   , $2000.00)

INSERT INTO Employees VALUES(12, 7   , 'Ron'     , $2000.00)

INSERT INTO Employees VALUES(13, 7   , 'Dan'     , $2000.00)

INSERT INTO Employees VALUES(14, 11  , 'James'   , $1500.00)

 

查询向Employees表插入的全部数据SQL语句：

 

select * from Employees

 

结果如图：

 

 

再建表二部门表（Departments）：

 

CREATE TABLE Departments

(

  deptid    INT NOT NULL PRIMARY KEY,

  deptname  VARCHAR(25) NOT NULL,

  deptmgrid INT NULL REFERENCES Employees

)

 

向Departments表插入数据：

 

INSERT INTO Departments VALUES(1, 'HR',           2)

INSERT INTO Departments VALUES(2, 'Marketing',    7)

INSERT INTO Departments VALUES(3, 'Finance',      8)

INSERT INTO Departments VALUES(4, 'R&D',          9)

INSERT INTO Departments VALUES(5, 'Training',     4)

INSERT INTO Departments VALUES(6, 'Gardening', NULL)

 

查询向Departments表插入的全部数据SQL语句：

 

select * from Departments

 

结果如图：

 

 

下面的表值函数使用雇员 ID 作为参数，并返回该雇员及他/她的所有下属：

 

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

 

返回每个部门经理的所有级别的全部下属，使用下面的SQL语句（用到了apply的cross apply）：

 

SELECT *

FROM Departments AS D

  CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST

 

结果如图：