Sql学习第六天——SQL 巩固练习（用到了前几天几个知识点）

SQL 巩固练习——用到了前几天几个知识点

　　在查看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 作为参数，并返回该雇员及他/她的所有下属（用到了前面所学的CTE公共表表达式 with...as... 的递归）：

 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

结果如图：

作者： Leo_wl

出处： http://www.cnblogs.com/Leo_wl/

本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。

版权信息

声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did46264

更新时间：2022-09-24 阅读：33次