【部门 用户表设计】

[十月流氓]

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，那么这个也是需要考虑的问题，方法和上面的一样

[十月流氓]

引用 1 楼 yupeigu 的回复:

我原来的公司的组织表，就是这么设计的有一个sub_code就是001，然后下属是001或者002 依次排下去， 如果有，比如原来是2级节点，现在变成了1级的节点，比如原来这个节点是001001，那么变成1级节点后，可能就是002，因为已经有一个001存在了，然后这个节点的下属，比如001001001，那么就得改成002001 也就是得改变所有的下属节点。 还有另一个问题是，分支的移动，比如原来是001001的，现在要把这个2级节点，移动到另一个一级节点上，也就是变成002001，那么这个也是需要考虑的问题，方法和上面的一样

嗯，这就是我担心的问题，组织结构节点可能频繁变动，把用户查询的压力加到了修改组织节点上，每次修改节点，同时要修改判断的东西很多，不知道怎么做才是合理的

[阳泉酒家小当家]

引用 2 楼 starye_837 的回复:

[quote=引用 1 楼 yupeigu 的回复:] 我原来的公司的组织表，就是这么设计的有一个sub_code就是001，然后下属是001或者002 依次排下去， 如果有，比如原来是2级节点，现在变成了1级的节点，比如原来这个节点是001001，那么变成1级节点后，可能就是002，因为已经有一个001存在了，然后这个节点的下属，比如001001001，那么就得改成002001 也就是得改变所有的下属节点。 还有另一个问题是，分支的移动，比如原来是001001的，现在要把这个2级节点，移动到另一个一级节点上，也就是变成002001，那么这个也是需要考虑的问题，方法和上面的一样

嗯，这就是我担心的问题，组织结构节点可能频繁变动，把用户查询的压力加到了修改组织节点上，每次修改节点，同时要修改判断的东西很多，不知道怎么做才是合理的[/quote] 你的组织有多少记录呢？

[阳泉酒家小当家]

你看看这个例子，就是递归查询的，自动生成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
/*
id	deprtid	deprtname
1	0	    1
2	1	    2
4	2	    4
6	4	    6
3	1	    3
5	3	    5
7	5	    7
*/

[铁歌]

部门表

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

[十月流氓]

如果是递归的话怎么查询？ select 员工表 where 所属部门 in （所要选的那个部门的所有子部门组成的以逗号为间隔的字符串）--这句怎么写？

[zlp321002]

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方式不慢啊。