【组织用户表设计】

2014-01-03

【部门用户表设计】2张表采用的都是自增长ID，部门是个树需求如下：点击任何一级部门都能查询出该部门以下所

【部门用户表设计】
2张表采用的都是自增长ID，部门是个树
需求如下：点击任何一级部门都能查询出该部门以下所有部门用户，点击根节点显示全部用户

用户表有部门ID字段，但是递归查询效率太低，我想在部门表、用户表中加一个CODE，比如0代表根节点，001代表一级节点，001001二级，以此类推，这样查询的时候就可以模糊查询出想要的结果，但是如果部门节点变了，比如以前是二级节点，改成一级节点，那还要改所有以前为二级节点用户的部门CODE为一级节点？

这2个表应该如何设计呢，CODE怎么生成，修改部门节点怎么处理..
[解决办法]
我原来的公司的组织表，就是这么设计的有一个sub_code就是001，然后下属是001或者002 依次排下去，

如果有，比如原来是2级节点，现在变成了1级的节点，比如原来这个节点是001001，那么变成1级节点后，可能就是002，因为已经有一个001存在了，然后这个节点的下属，比如001001001，那么就得改成002001 也就是得改变所有的下属节点。

还有另一个问题是，分支的移动，比如原来是001001的，现在要把这个2级节点，移动到另一个一级节点上，也就是变成002001，那么这个也是需要考虑的问题，方法和上面的一样
[解决办法]
你看看这个例子，就是递归查询的，自动生成code，就是里面的sort字段：

--drop table tb

create table tb(ID int, DeprtID int, DeprtName varchar(10))

insert into tb
select 1,   0,        '1'        
union all select 2 ,  1 ,       '2'
union all select 3 ,  1 ,       '3'
union all select 4 ,  2 ,       '4'
union all select 5 ,  3 ,       '5'
union all select 6 ,  4 ,       '6'
union all select 7 ,  5,        '7'
go


;with t
as
(
select id,DeprtID,DeprtName,1 as level,
       cast(right('000'+cast(id as varchar),3) as varchar(max)) as sort
from tb
where DeprtID =0

union all

select tb.id,tb.DeprtID,tb.DeprtName,level + 1 ,
       cast(sort+right('000'+cast(tb.id as varchar),3) as varchar(max))
from t
inner join tb 
        on t.id = tb.DeprtID
)

select *
from t
order by sort
/*
iddeprtiddeprtname
10    1
21    2
42    4
64    6
31    3
53    5
75    7
*/

[解决办法]
部门表

create table dept
(
DeptID int identity(1,1) primary key,
parentID INT ,--自关联DEPTID
Level tinyint,--层级 
createdOn datetime
)

[解决办法]
WITH    SimpleRecursive(员工名称, 员工ID, 所属部门 )
          AS ( SELECT   员工名称 ,
                        员工ID ,
                        所属部门
               FROM     员工表树
               WHERE    员工ID = '某员工ID'
               UNION ALL
               SELECT   p.员工名称,
                        p.员工ID,
                        p.所属部门
               FROM     员工表树 p
                        INNER JOIN SimpleRecursive A ON A.员工ID = p.所属部门
             )
    SELECT  sr.员工ID AS empid ,
            sr.员工名称 AS Emp ,
            et.员工名称上级 AS Boss

FROM SimpleRecursive sr
INNER JOIN 员工表树 et ON sr.所属部门 = et.员工ID

CTE方式不慢啊。

热点排行

SQL Server

【组织 用户表设计】

【组织用户表设计】