SQL多级关系的表示

我们要建立多级关系，首先需要两个表.
        第一个表表示直属结构，比如说我们公司有一个部门。而这个部门下还有3个小组，那么3个小组属于这个部门.第二个表表示多层结构,就象第一表里面所表示的，这三个小组同时还属于公司.而这个公司同时属于这3个小组的父级的父级的部门。假如我们只有第一个表来表示关系的话。那么会很复杂。并且查询困难.所以需要两个表来表示他们之间的关系.
或许你现在还没有认识到为什么需要第二个表。
       那么现在我来给你说下。我现在有5个公司，并且每个公司下有5个部门。然后每个部门下有3个小组，而每个小组下有未知员工。然后我要查询其中一个公司的所有员工。那么你查询起来就很复杂了，首先你要查询出该公司下的所属部门，然后查询所属部门的所有小组，然后再查询所属小组的所有员工。这样才能查询到员工。
      但是，事情永远没有这么简单。比如说我有直属部门的员工。也就是说，他不属于小组，直接属于员工。那么你怎么办，只能union 或者用临时表。你可以说，我们可以把他们限制一下，员工不能属于部门，但是这是不合理的。甚至有可能他这个公司到小组结束了。另外一个公司却不止3层部门。那么你怎么办。不可能每个公司都搞个SQL语句吧。所以这时候就需要第二个表了。
     第二个表会表示出所有上级部门的关系，比如说：我有个公司，公司下面有个销售部，销售部下面有个销售一部，而销售一部下面有个销售一组.那么我们要查询这个销售一组的所有父级部门

本部门       父级部门
销售一组   销售一部
销售一组   销售部
销售一组   么么公司

这样就很清楚了，因为我们只需要查
select 父级部门 from tab where 本部门='销售一组'
就全部出来了.而查询该公司下的所有部门则
select 本部门 from tab where 父级部门='么么公司'
该公司下的所有部门就出来了，然后我们只需要 inner join 一下，查询出部门的所有员工。很简单吧!
我们现在思想说的差不多了，现在来说一下怎么具体实现。(我的目的是表示所有的多级关系，但是不包含历史记录的,比如说员工从A部门移动到b部门,我的里面都没有用事物处理，我是为了方便调用，比如移动员工吧，你首先update 下员工表，然后写入员工的历史记录。意思是员工在哪个部门下工作过)
实现环境 :SQL 2005
首先建立表:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Setting_Node](--直属表
[intNodeId] [int] IDENTITY(1,1) NOT NULL,
[intParNodeId] [int] NOT NULL,
[intTableId] [int] NOT NULL,                --Setting_Table
[strNode] [varchar](400) COLLATE Chinese_PRC_CI_AS NULL,
[strMemo] [varchar](500) COLLATE Chinese_PRC_CI_AS NULL,
[btIsDelete] [bit] NOT NULL CONSTRAINT [DF_Setting_Node_btIsDelete] DEFAULT ((0)),
[intCreateOperaterId] [int] NOT NULL CONSTRAINT [DF_Table_1_intOperaterId] DEFAULT ((0)),
[intUpdateOperaterId] [int] NOT NULL CONSTRAINT [DF_Setting_Node_intUpdateOperaterId] DEFAULT ((0)),
[dtCreate] [datetime] NOT NULL CONSTRAINT [DF_Setting_Node_dtCreate] DEFAULT (getdate()),
[dtUpdate] [datetime] NOT NULL CONSTRAINT [DF_Setting_Node_dtUpdate] DEFAULT (getdate()),
CONSTRAINT [PK_Setting_Node] PRIMARY KEY CLUSTERED
(
[intNodeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

/****** 对象: Table [dbo].[Setting_Layer]    脚本日期: 05/07/2010 12:14:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Setting_Layer](--多层表示表
[intLayerId] [int] IDENTITY(1,1) NOT NULL,
[intNodeId] [int] NOT NULL,                     --Setting_Node
[intParNodeId] [int] NOT NULL,                --Setting_Node
[dtCreate] [datetime] NOT NULL CONSTRAINT [DF_Setting_Layer_dtCreate] DEFAULT (getdate()),
[dtEnd] [datetime] NULL,
[btIsDelete] [bit] NOT NULL CONSTRAINT [DF_Setting_Layer_btIsDelete] DEFAULT ((0)),
CONSTRAINT [PK_Setting_Layer] PRIMARY KEY CLUSTERED
(
[intLayerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

/****** 对象: Table [dbo].[Setting_Table]    脚本日期: 05/07/2010 12:15:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Setting_Table]( --所属的表
[intTableId] [int] IDENTITY(1,1) NOT NULL,
[strTable] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[strKey] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[strMemo] [varchar](500) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_Setting_Table] PRIMARY KEY CLUSTERED
(
[intTableId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

这三个表就是我们需要操作的表，一个是 Setting_Table ，因为我们可能表示多级关系，比如说员工表 (Setting_Operate) ，我们需要表示多级关系;另外还有其他表，比如说树等等.

获取 Setting_Table的函数:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/* 获取表的ID，以便进行下一步操作 */
Create FUNCTION [dbo].[fun_GetTableId]
(
@strKey varchar(100)='',
@strTable varchar(200)=''
)
RETURNS int
AS
BEGIN
DECLARE @Rt int

IF @strKey<>''
BEGIN
   SELECT @Rt =intTableId FROM dbo.Setting_Table WHERE strKey=@strKey
END
ELSE IF @strTable<>''
BEGIN
   SELECT @Rt =intTableId FROM dbo.Setting_Table WHERE strTable=@strTable  
END
ELSE
BEGIN
   SET @Rt=0
END

SET @Rt=ISNULL(@Rt,0)
  
RETURN @Rt
END

插入一个结点的存储过程:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Create PROCEDURE [dbo].[proc_Setting_Node_Create]
    (
      @intParNodeId INT = 0 ,
      @intTableId INT = 0 ,
      @strNode VARCHAR(400) = '' ,
      @strMemo VARCHAR(500) = '' ,
      @intOperateId INT = 0
    )
AS
    BEGIN
        SET NOCOUNT ON ;
        DECLARE @Rt INT
        INSERT INTO dbo.Setting_Node
                ( intParNodeId ,
                  intTableId ,
                  strNode ,
                  strMemo ,
                  btIsDelete ,
                  intCreateOperaterId ,
                  intUpdateOperaterId ,
                  dtCreate ,
                  dtUpdate
           )
        VALUES ( @intParNodeId , -- intParNodeId - int
                  @intTableId , -- intTableId - int
                  @strNode , -- strNode - varchar(400)
                  @strMemo , -- strMemo - varchar(500)
                  0 , -- btIsDelete - bit
                  @intOperateId , -- intCreateOperaterId - int
                  @intOperateId , -- intUpdateOperaterId - int
                  GETDATE() , -- dtCreate - datetime
                  GETDATE() -- dtUpdate - datetime
           )
        
        SET @Rt = SCOPE_IDENTITY()

        DECLARE @parId INT
        SET @parId = @intParNodeId
        WHILE @parId <> 0
            BEGIN
                INSERT INTO dbo.Setting_Layer
                        ( intNodeId ,
                          intParNodeId ,
                          dtCreate ,
                          dtEnd ,
                          btIsDelete
              )
                VALUES ( @Rt , -- intNodeId - int
                          @parId , -- intParNodeId - int
                          GETDATE() , -- dtCreate - datetime
                          NULL , -- dtEnd - datetime
                          0 -- btIsDelete - bit
              )
                SELECT @parId = intParNodeId
                FROM dbo.Setting_Node
                WHERE   intNodeId = @parId
            END
         RETURN @Rt
    END

插入的调用方法:
条件数据:
Setting_Table数据

1

Setting_Operater

Setting_Operater

操作员

插入执行:
DECLARE @intTableId INT
DECLARE @intParId INT
EXEC @intTableId=dbo.fun_GetTableId
@strKey = 'Setting_Operater', -- varchar(100)
    @strTable = 'Setting_Operater' -- varchar(200)
--SELECT @intTableId

DECLARE @intTotalId INT

EXEC @intParId=dbo.proc_Setting_Node_Create
@intParNodeId = 0, -- int
    @intTableId = @intTableId, -- int
    @strNode = '么么公司', -- varchar(400)
    @strMemo = '么么公司', -- varchar(500)
    @intOperateId = 0 -- int
   
SET @intTotalId=@intParId

EXEC @intParId=dbo.proc_Setting_Node_Create
@intParNodeId = @intTotalId, -- int
    @intTableId = @intTableId, -- int
    @strNode = '销售部', -- varchar(400)
    @strMemo = '销售部', -- varchar(500)
    @intOperateId = 0 -- int
/*-----------------------------------------------------------------------*/
DECLARE @intSaleDeptId INT
SET @intSaleDeptId=@intParId
EXEC @intParId=dbo.proc_Setting_Node_Create
@intParNodeId = @intSaleDeptId, -- int
    @intTableId = @intTableId, -- int
    @strNode = '销售一部', -- varchar(400)
    @strMemo = '销售一部', -- varchar(500)
    @intOperateId = 0 -- int

EXEC @intParId=dbo.proc_Setting_Node_Create
@intParNodeId = @intSaleDeptId, -- int
    @intTableId = @intTableId, -- int
    @strNode = '销售二部', -- varchar(400)
    @strMemo = '销售二部', -- varchar(500)
    @intOperateId = 0 -- int
   
EXEC @intParId=dbo.proc_Setting_Node_Create
@intParNodeId = @intSaleDeptId, -- int
    @intTableId = @intTableId, -- int
    @strNode = '销售三部', -- varchar(400)
    @strMemo = '销售三部', -- varchar(500)
    @intOperateId = 0 -- int
   
EXEC @intParId=dbo.proc_Setting_Node_Create
@intParNodeId = @intSaleDeptId, -- int
    @intTableId = @intTableId, -- int
    @strNode = '销售四部', -- varchar(400)
    @strMemo = '销售四部', -- varchar(500)
    @intOperateId = 0 -- int

/*-----------------------------------------------------------------------*/
EXEC @intParId=dbo.proc_Setting_Node_Create
@intParNodeId = @intTotalId, -- int
    @intTableId = @intTableId, -- int
    @strNode = '财务部', -- varchar(400)
    @strMemo = '财务部', -- varchar(500)
    @intOperateId = 0 -- int

EXEC @intParId=dbo.proc_Setting_Node_Create
@intParNodeId = @intTotalId, -- int
    @intTableId = @intTableId, -- int
    @strNode = '仓库部', -- varchar(400)
    @strMemo = '仓库部', -- varchar(500)
    @intOperateId = 0 -- int

EXEC @intParId=dbo.proc_Setting_Node_Create
@intParNodeId = @intTotalId, -- int
    @intTableId = @intTableId, -- int
    @strNode = '物流部', -- varchar(400)
    @strMemo = '物流部', -- varchar(500)
    @intOperateId = 0 -- int

SELECT * FROM dbo.Setting_Layer

SELECT * FROM dbo.Setting_Node

--TRUNCATE TABLE dbo.Setting_Node
--TRUNCATE TABLE dbo.Setting_Layer

SELECT a.intNodeId ,
        a.strNode ,
        c.intNodeId ,
        c.strNode
FROM    dbo.Setting_Node AS a
        INNER JOIN dbo.Setting_Layer AS b ON a.intNodeId = b.intNodeId
        INNER JOIN dbo.Setting_Node AS c ON b.intParNodeId = c.intNodeId
好了，我们就搞到这里，相信删除和修改已经很容易了。大家能够搞定，两个表能够解决很多问题。第一，查询效率的提升。第二，结构的清楚理解