刚接触MSSQL，现在想做个view不会做，求帮助,该如何处理

刚接触MSSQL，现在想做个view不会做，求帮助
现在有个这样的表
ID aid name parentID iddegree
1 a1 1 0 0
2 a2 2 a1 1
3 a3 3 a1 1
4 a4 4 a1 1
5 a5 5 a2 2
6 a6 6 a2 2
7 a7 7 a3 2
8 a8 8 a5 3
9 a9 9 a8 4
...

这一类似这样的表每个name 都有唯一的aid，parentId对应的是属于哪个父ID，iddegree表示层级，类似的人员表，应该很常见。我初接触这个，很多查询都不是很熟练，想做个以 iddegree为1 的分别的检视表，就是a1,a2,a3,a4的下层的人员表。要怎么弄呢？（我一开始的需求是给个参数如a2判断选出所有属于a2的部门人员，显示出a2这样的表）

------解决方案--------------------

SQL code


--ID aid name parentID iddegree
if OBJECT_ID('tf_getchildInfo','tf') is not null drop function tf_getchildInfo
go
create function tf_getchildInfo(@AID varchar(10))
returns @table table
(
    ID int,
    AID varchar(10),
    Name int,
    ParentID varchar(10),
    Iddegree int
)
as
Begin
    with tb1 as
    (
        --虚拟出一张表
            select 1 as id,'a1' as aid,1 as name,'' as parentID,0 as iddegree union all
            select 2,'a2',2,'a1',1 union all 
            select 3,'a3',3,'a1',1 union all 
            select 4,'a4',4,'a1',1 union all 
            select 5,'a5',5,'a2',2 union all 
            select 6,'a6',6,'a2',2 union all 
            select 7,'a7',7,'a3',2 union all 
            select 8,'a8',8,'a5',3 union all 
            select 9,'a9',9,'a8',4 
    ),
    cte as 
    (
        --使用递归功能
        select id,aid,name,parentID,iddegree from tb1
        where aid=@AID-- 使用传递的参数
        union all
        select tb1.id,tb1.aid,tb1.name,tb1.parentID,tb1.iddegree  from cte 
        join tb1 on tb1.parentID=cte.aid
    )
        --把数据放到一张变量表
    insert into @table(id,aid,name,parentID,iddegree)
    select * from cte
    return
end
go
select * from dbo.tf_getchildInfo('a1')
go
/*
ID          AID        Name        ParentID   Iddegree
----------- ---------- ----------- ---------- -----------
1           a1         1                      0
2           a2         2           a1         1
3           a3         3           a1         1
4           a4         4           a1         1
7           a7         7           a3         2
5           a5         5           a2         2
6           a6         6           a2         2
8           a8         8           a5         3
9           a9         9           a8         4

(9 row(s) affected)


*/

------解决方案--------------------

SQL code


if OBJECT_ID('v_childInfo','V') is not null drop view v_childInfo
go
create view v_childInfo
as
with tb1 as
    (
        --虚拟出一张表
            select 1 as id,'a1' as aid,1 as name,'' as parentID,0 as iddegree union all
            select 2,'a2',2,'a1',1 union all 
            select 3,'a3',3,'a1',1 union all 
            select 4,'a4',4,'a1',1 union all 
            select 5,'a5',5,'a2',2 union all 
            select 6,'a6',6,'a2',2 union all 
            select 7,'a7',7,'a3',2 union all 
            select 8,'a8',8,'a5',3 union all 
            select 9,'a9',9,'a8',4 
    ),
    cte as 
    (
        --使用递归功能
        select id,aid,name,parentID,iddegree from tb1
        where aid='a5'-- 使用传递的参数
        union all
        select tb1.id,tb1.aid,tb1.name,tb1.parentID,tb1.iddegree  from cte 
        join tb1 on tb1.parentID=cte.aid
    )
    select * from cte
go
select * from v_childInfo
go
/*
id          aid  name        parentID iddegree
----------- ---- ----------- -------- -----------
5           a5   5           a2       2
8           a8   8           a5       3
9           a9   9           a8       4

(3 row(s) affected)


*/

刚接触MSSQL，现在想做个view不会做，求帮助,该如何处理

相关推荐