一个类似权限挂载的设计

DECLARE @A TABLE
    (
      [Aid] INT PRIMARY KEY NOT NULL ,
      [name] NVARCHAR(50) NOT NULL
    ) ;

INSERT  INTO @A( Aid, name )
VALUES  ( 1, N‘状态1‘)
INSERT  INTO @A( Aid, name )
VALUES  ( 2, N‘状态2‘)
INSERT  INTO @A( Aid, name )
VALUES  ( 3, N‘状态3‘)

DECLARE @B TABLE
    (
      [Bid] INT PRIMARY KEY NOT NULL ,
      [Aid] INT NOT NULL ,
      [Name] NVARCHAR(50) NOT NULL
    );

INSERT  INTO @B( Bid, Aid, Name )
VALUES  ( 1, -- id - int name就可以说是状态1下的动作1
          1,  -- Aid - int
          N‘状态1下的动作1‘ )
INSERT  INTO @B( Bid, Aid, Name )
VALUES  ( 2, -- id - int  name就可以说是状态1下的动作2
          1,  -- Aid - int
          N‘状态1下的动作2‘ )
INSERT  INTO @B( Bid, Aid, Name )
VALUES  ( 3, -- id - int name就可以说是状态2下的动作1
          2,  -- Aid - int
          N‘状态2下的动作1‘ )

INSERT  INTO @B( Bid, Aid, Name )
VALUES  ( 4, -- id - int  name就可以说是状态2下的动作2
          2,  -- Aid - int
          N‘状态2下的动作2‘ ) 
INSERT  INTO @B( Bid, Aid, Name )
VALUES  ( 5, -- id - int name就可以说是状态2下的动作3
          2,  -- Aid - int
          N‘状态2下的动作3‘ )     
INSERT  INTO @B( Bid, Aid, Name )
VALUES  ( 6, -- id - int name就可以说是状态3下的动作1
          3,  -- Aid - int
          N‘状态3下的动作1‘ )  

DECLARE @C TABLE
    (
      [Cid] INT PRIMARY KEY NOT NULL ,
      [Aid] INT NOT NULL ,
      [Deptid] INT NOT NULL
    );

INSERT  INTO @C( Cid, Aid, Deptid )
VALUES  ( 1, -- id - int
          1, -- Aid - int  状态1
          1  -- Deptid - int
          )     
INSERT  INTO @C( Cid, Aid, Deptid )
VALUES  ( 2, -- id - int
          2, -- Aid - int  状态2      
          1  -- Deptid - int
          )
INSERT  INTO @C( Cid, Aid, Deptid )
VALUES  ( 3, -- id - int
          3, -- Aid - int   状态3
          1  -- Deptid - int
          )
------50321
INSERT  INTO @C( Cid, Aid, Deptid )
VALUES  ( 4, -- id - int
          1, -- Aid - int   状态1
          50321  -- Deptid - int
          )     
INSERT  INTO @C( Cid, Aid, Deptid )
VALUES  ( 5, -- id - int
          2, -- Aid - int   状态2
          50321  -- Deptid - int
          )
INSERT  INTO @C( Cid, Aid, Deptid )
VALUES  ( 6, -- id - int
          3, -- Aid - int   状态3
          50321  -- Deptid - int
          )

DECLARE @D TABLE
    (
      [Did] INT PRIMARY KEY IDENTITY(1, 1)NOT NULL ,
      [Bid] INT NOT NULL ,
      [Cid] INT NOT NULL
    );

INSERT  INTO @D( Bid, Cid )
VALUES  ( 1, -- Bid - int    状态1下的动作1
          1  -- Cid - int     部门1  和状态1
          ) 

INSERT  INTO @D( Bid, Cid )
VALUES  ( 2, -- Bid - int     状态1下的动作2
          1  -- Cid - int      部门1  和状态1
          ) 

INSERT  INTO @D( Bid, Cid )
VALUES  ( 4, -- Bid - int      状态2下的动作2
          2  -- Cid - int      部门1  和状态2
          ) 

INSERT  INTO @D( Bid, Cid )
VALUES  ( 5, -- Bid - int       状态2下的动作3
          1  -- Cid - int        部门1  和状态2
          ) 

/*
A表就是状态列表 
B表就是每个状态下对应的动作列表
C就是每个状态拥有那些部门     或者说部门下拥有那些状态
D就是部门下拥有的状态的动作
*/
--现在要往  D表复制一份 部门1的数据
/*

/*已配置的状态，可配置的动作*/
SELECT  b.Bid ,
        c.Cid ,
        A.name AS ‘状态‘ ,
        B.NAME AS ‘动作‘
FROM    @C AS c
        LEFT JOIN @A AS a ON C.Aid = a.Aid
        LEFT JOIN @B AS b ON a.Aid = b.Aid
WHERE   c.Deptid = 1

SELECT  b.Bid ,
        c.Cid ,
        A.name AS ‘状态‘ ,
        B.NAME AS ‘动作‘
FROM    @C AS c
        LEFT JOIN @A AS a ON C.Aid = a.Aid
        LEFT JOIN @B AS b ON a.Aid = b.Aid
WHERE   c.Deptid = 50321

/*1部门已配置的4个动作*/
SELECT  b.bid ,c.cid ,b.Name
FROM    @D AS d
        LEFT JOIN @C AS c ON d.Cid = c.Cid
        LEFT JOIN @B AS b ON d.Bid = b.Bid
WHERE   c.Deptid = 1

INSERT  INTO @D( Bid ,Cid )
        SELECT  v1.Tbid ,v1.Tcid
        FROM    ( SELECT    b.bid AS Tbid ,c.cid AS Tcid
                  FROM      @B AS b
                            LEFT JOIN @A AS a ON a.aid = b.Aid
                            LEFT JOIN @C AS c ON a.aid = c.Aid
                  WHERE     c.Deptid = 50321
                ) v1
                INNER JOIN ( SELECT b.bid AS bid ,c.cid AS cid
                             FROM   @B AS b
                                    LEFT JOIN @A AS a ON a.aid = b.Aid
                                    LEFT JOIN @C AS c ON a.aid = c.Aid
                                    RIGHT JOIN @D AS d ON b.bid = d.bid
                             WHERE  c.Deptid = 1
                           ) V2 ON v1.Tbid = V2.bid

SELECT  b.bid ,c.cid ,b.Name
FROM    @D AS d
        LEFT JOIN @C AS c ON d.Cid = c.Cid
        LEFT JOIN @B AS b ON d.Bid = b.Bid
WHERE   c.Deptid = 50321